Extract multiple rows using a single criteria

[danhodges99]

Hi,

I have the following example data:

A	B	C	D	E
Include?	Date	Channel	Event	Viewers
	18/01/17	Channel 1	TeamA v TeamB	50,000
	18/01/17	Channel 2	TeamC v TeamD	75,000
Include	19/01/17	Channel 1	TeamA v TeamE	100,000
Include	19/01/17	Channel 2	TeamB v TeamD	98,000
Include	19/01/17	Channel 3	TeamF v TeamH	25,000
Include	19/01/17	Channel 1	TeamC v TeamJ	1,000
	19/01/17	Channel 2	TeamZ v TeamR	563
	20/01/17	Channel 3	TeamG v TeamK	24,500

<tbody>
</tbody>

I need to extract only the lines which are tagged to be included in column A into a separate table. I know I can obviously do this via a filter, but need to significantly re-format so filtering is not appropriate.

The final report would ideally look something like this:

Event	Channel	Viewers
TeamA v TeamE	Channel 1	100,000
TeamB v TeamD	Channel 2	98,000
TeamF v TeamH	Channel 3	25,000
TeamC v TeamJ	Channel 1	1,000

<tbody>
</tbody>

I can obviously use VLOOKUP to return the first matching row, but I'm getting stuck trying to feed all the other matches into the report table - any help much appreciated!

 

[AhoyNC]

Here is one way.
Note: the IFERROR and AGGREGATE function are only available in Excel 2010 and higher. If you have a ver. before 2010 let us known.

Formula in A14 for Row ID would be copied down.
Formula in B14 would be dragged down and across as needed.

Excel Workbook
	A	B	C	D	E
1	Include?	Date	Channel	Event	Viewers
2		18/01/17	Channel 1	TeamA v TeamB	50,000
3		18/01/17	Channel 2	TeamC v TeamD	75,000
4	Include	19/01/17	Channel 1	TeamA v TeamE	100,000
5	Include	19/01/17	Channel 2	TeamB v TeamD	98,000
6	Include	19/01/17	Channel 3	TeamF v TeamH	25,000
7	Include	19/01/17	Channel 1	TeamC v TeamJ	1,000
8		19/01/17	Channel 2	TeamZ v TeamR	563
9		20/01/17	Channel 3	TeamG v TeamK	24,500
10
11
12
13	Row ID	Event	Channel	Viewers
14	3	TeamA v TeamE	Channel 1	100000
15	4	TeamB v TeamD	Channel 2	98000
16	5	TeamF v TeamH	Channel 3	25000
17	6	TeamC v TeamJ	Channel 1	1000
18
Sheet

 

[Eric W]

Here's a version that doesn't use AGGREGATE.

	A	B	C	D	E	F	G	H	I	J	K
1	Include?	Date	Channel	Event	Viewers			Row	Event	Channel	Viewers
2		18/01/17	Channel 1	TeamA v TeamB	50,000			4	TeamA v TeamE	Channel 1	100000
3		18/01/17	Channel 2	TeamC v TeamD	75,000			5	TeamB v TeamD	Channel 2	98000
4	Include	19/01/17	Channel 1	TeamA v TeamE	100,000			6	TeamF v TeamH	Channel 3	25000
5	Include	19/01/17	Channel 2	TeamB v TeamD	98,000			7	TeamC v TeamJ	Channel 1	1000
6	Include	19/01/17	Channel 3	TeamF v TeamH	25,000
7	Include	19/01/17	Channel 1	TeamC v TeamJ	1,000
8		19/01/17	Channel 2	TeamZ v TeamR	563
9		20/01/17	Channel 3	TeamG v TeamK	24,500

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I2 =IF($H2="","",INDEX($D:$D,$H2)) J2 =IF($H2="","",INDEX($C:$C,$H2)) K2 =IF($H2="","",INDEX($E:$E,$H2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula H2 {=IFERROR(SMALL(IF($A$2:$A$9="Include",ROW($A$2:$A$9)),ROWS($H$2:$H2)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The ROW column is designed to perform common calculations once, so that the other columns calculate quicker. You can hide the Row column after you enter the formula.

Hope this helps.

 

[danhodges99]

Guys - thanks for the responses this now works brilliantly - in your debt!

 

[AhoyNC]

You're welcome. Thanks for the feedback.