Extract entire column if cell matches - then move on

trentonirons

New Member
Joined
Mar 27, 2017
Messages
17
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:

First Name
Last Name
Email
Course Name
Pre-Status
Post-Status
Username
John
Smith
johnsmith@company.com
Test Course
Approved
No-Show
JOHNSMITH

<tbody>
</tbody>




<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
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?
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top