Extract entire column if cell matches - then move on

[trentonirons]

Hi all,

I don't know where to start on this one. I have a range of data with about 7 columns of data. This list contains about 5000 rows of information in the following format:

[TABLE="width: 1659"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]First Name
[/TD]
[TD]Last Name
[/TD]
[TD]Email
[/TD]
[TD]Course Name
[/TD]
[TD]Pre-Status
[/TD]
[TD]Post-Status
[/TD]
[TD]Username
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]Smith
[/TD]
[TD]johnsmith@company.com
[/TD]
[TD]Test Course
[/TD]
[TD]Approved
[/TD]
[TD]No-Show
[/TD]
[TD]JOHNSMITH
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need a formula that will return a list of all occurrences of each row that has a Post-Status of 'No-Show'. From a logic standpoint, the formula should check each row and say "if Post-status equals "No-Show", return E-mail, and Course Name. Then in the cell next to it, it should search the rest of the range starting from the point where the previous cell found the last occurrence of no-show. That way, I can get a list of all no-shows from the range. Where do I start on that? What functions should I be using?

 

[CyrusTheVirus]

Hey Trenton,

There a number of ways to do this. Using Power Query wouldn't be too difficult. Have you ever used it? Formulas can also be provided...

Also, what are your expected results? Can you please show a few expected results so that the viewers can see exactly what you want in the end?