Forum Discussion
Develop/Extract List?
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 | |
Maybe 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.
3 Replies
Maybe 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.
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 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.
