SOLVED

Develop/Extract List?

New Contributor

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

 

 

CourseCreditsTaken
Structural Analysis3x
Estimating II3Su22
Construction Law3F22
Mechanical Electrical3F22
Construction Graphics3S23
Construction Surveying2x
Quality Management3F22
   
Summer 2022  
CourseCredits 
   
Fall 2022  
CourseCredits 
   
Spring 2023  
CourseCredits 
   
3 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@beachfarmer 

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.

@Quadruple_Pawn 

 

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 

 

CourseCreditsTaken CourseCreditsTaken
Structural Analysis3x Construction Graphics3S23
Estimating II3Su22 Construction Surveying2x
Construction Law3F22 Quality Management3F2
Mechanical Electrical3F22    
       
       
       
       
Summer 2022      
CourseCredits     
       
Fall 2022      
CourseCredits     
       
Spring 2023      
CourseCredits     

 

@beachfarmer 

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.