Mar 10 2022 07:06 AM
Greetings:
I would like to develop a list/extract data from the list of courses (column a) below from the text designation in column c (taken). As such, I'm looking for a formula to insert in the cell(s) under the headings below the table for Summer 2022 (Su22), Fall 2022 (F22) and Spring 2023 (S23). So, based upon the proper formula(s) to be put under each header, the courses listed under the Summer 2022 would be Estimating II 3 (for credits); under Fall 2022 would be Construction Law 3, Mechanical Electrical 3, Quality Management and under Spring 2023 would be Construction Graphics 3.
I don't necessarily need the formula to add rows below the headers....there will never be more than 5 or 6 courses (rows) under each header and I can replicated the formula, etc
Any help is greatly appreciated...thanks...John
Course | Credits | Taken |
Structural Analysis | 3 | x |
Estimating II | 3 | Su22 |
Construction Law | 3 | F22 |
Mechanical Electrical | 3 | F22 |
Construction Graphics | 3 | S23 |
Construction Surveying | 2 | x |
Quality Management | 3 | F22 |
Summer 2022 | ||
Course | Credits | |
Fall 2022 | ||
Course | Credits | |
Spring 2023 | ||
Course | Credits | |
Mar 10 2022 07:31 AM
SolutionMaybe as shown in the attached file. I entered the formula below and it seems to work in my sheet.
=INDEX(A$2:A$8,SMALL(IF($C$2:$C$8="SU22",ROW($A$1:$A$7)),ROW(A1)))
Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Mar 10 2022 01:23 PM
thank you!...this works! Can I put another twist on my problem?
The native format of my data is shown below where there are two different "columns of information" (my first example is a single "column of information" whereby I have to cut the second "column of information" and past it under the first "column of information" to complete the analysis with the formula you provided.
Is there a formula possible that will search both "columns of information" and list the info under the headers just is in the first example I had?...thanks again...John
Course | Credits | Taken | Course | Credits | Taken | |
Structural Analysis | 3 | x | Construction Graphics | 3 | S23 | |
Estimating II | 3 | Su22 | Construction Surveying | 2 | x | |
Construction Law | 3 | F22 | Quality Management | 3 | F2 | |
Mechanical Electrical | 3 | F22 | ||||
Summer 2022 | ||||||
Course | Credits | |||||
Fall 2022 | ||||||
Course | Credits | |||||
Spring 2023 | ||||||
Course | Credits |
Mar 10 2022 02:38 PM
I don't have a formula to do that. I think you did absolutely the right thing to copy the two tables under each other. Before creating formulas in Excel, one should always prepare the data in such a way that the simplest and most efficient evaluation possible can be achieved.
Mar 10 2022 07:31 AM
SolutionMaybe as shown in the attached file. I entered the formula below and it seems to work in my sheet.
=INDEX(A$2:A$8,SMALL(IF($C$2:$C$8="SU22",ROW($A$1:$A$7)),ROW(A1)))
Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.