SOLVED

New Contributor

# 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
3 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

# Re: Develop/Extract List?

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.

# Re: Develop/Extract List?

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

# Re: Develop/Extract List?

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.