Extract Rows with "X" in column A (Index Match solution?)

[lawi227]

I have a set of data (A1:H658). In column A, I want to put an "X" next to any rows that I want to pull over.

My traditional way of doing this is to do an IF statement that says IF(A1="X",B1,""). But this returns a lot of blanks. For example, if I have ~500 rows of data, and I put an "X" next to 200 rows, then I have 300 rows of blank data. I would traditionally highlight this list, and filter out the blanks.

There has to be a better way of only returning rows of data (from column B to H) to another worksheet, if there is an X in column A.

Here are my headers and columns:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Include in Assessment?[/TD]
[TD]Ref #[/TD]
[TD]SC, OF, or SCOF[/TD]
[TD]Excellence Category[/TD]
[TD]Function[/TD]
[TD]Sub-Function[/TD]
[TD]Process[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]1[/TD]
[TD]SC[/TD]
[TD]People[/TD]
[TD]Purchase[/TD]
[TD]Buyer[/TD]
[TD]HR[/TD]
[TD]BLAH BLAH BLAH[/TD]
[/TR]
</tbody>[/TABLE]

Does that make sense?

Thanks so much! I will forever being using this formulas, as I do a lot of this! Thanks!

 

[63falcondude]

Is filtering on column A and then copying and pasting an option? If not, you can try something like this:

Excel 2010
	A	B	C	D	E	F	G	H
1	Include in Assessment?	Ref #	SC, OF, or SCOF	Excellence Category	Function	Sub-Function	Process	Description
2	X	1	SC	People	Purchase	Buyer	HR	BLAH BLAH BLAH
3		2	OF	a	b	c	d	e
4	X	3	SCOF	f	g	h	i	j
Sheet1

Excel 2010
	A	B	C	D	E	F	G
1	Ref #	SC, OF, or SCOF	Excellence Category	Function	Sub-Function	Process	Description
2	1	SC	People	Purchase	Buyer	HR	BLAH BLAH BLAH
3	3	SCOF	f	g	h	i	j
4
Sheet2

Cell Formulas
Range	Formula
A2	{=IFERROR(INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4="X",ROW(Sheet1!$A$2:$A$4)-1),ROW(1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Drag the formula down and to the right. Adjust ranges as needed.

 

[lawi227]

Thank you! Can you provide an explanation briefly of how that formula works so I can build upon it in the future?

Also, if I do any filtering, will that mess up the formulas? Let's say I want to only view "HR" in column F on sheet 2.

Is filtering on column A and then copying and pasting an option? If not, you can try something like this:

Excel 2010

	A	B	C	D	E	F	G	H
X	SC	People	Purchase	Buyer	HR	BLAH BLAH BLAH
OF	a	b	c	d	e
X	SCOF	f	g	h	i	j

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Include in Assessment?[/TD]
[TD="align: center"]Ref #[/TD]
[TD="align: center"]SC, OF, or SCOF[/TD]
[TD="align: center"]Excellence Category[/TD]
[TD="align: center"]Function[/TD]
[TD="align: center"]Sub-Function[/TD]
[TD="align: center"]Process[/TD]
[TD="align: center"]Description[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

</tbody>

Sheet1​

Excel 2010

	A	B	C	D	E	F	G
SC	People	Purchase	Buyer	HR	BLAH BLAH BLAH
SCOF	f	g	h	i	j

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Ref #[/TD]
[TD="align: center"]SC, OF, or SCOF[/TD]
[TD="align: center"]Excellence Category[/TD]
[TD="align: center"]Function[/TD]
[TD="align: center"]Sub-Function[/TD]
[TD="align: center"]Process[/TD]
[TD="align: center"]Description[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4="X",ROW(Sheet1!$A$2:$A$4)-1),ROW(1:1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Drag the formula down and to the right. Adjust ranges as needed.

 

[63falcondude]

Thank you! Can you provide an explanation briefly of how that formula works so I can build upon it in the future?

You're welcome. The formula is similar to an INDEX MATCH formula but the key difference is that an INDEX MATCH formula returns the first match (where column A has an "X") where this one returns all of the matches.

The ROW(1:1) of the SMALL function is what changes the "k" (match number) when you drag the formula down.

Note that the range in the beginning of the INDEX function has the columns as relative (no "$", will change when dragging across) and the rows as absolute ("$", will not change).

 

[lawi227]

I am trying to use the same formula on a different set of data, and I had a follow-up question. When I use the formula, it does not return the correct cell reference. Let's say instead of "X", I search for "OU". I get a list of of data and all of them are off by the same number of rows. For example, If I select "Engr" and I'm supposed to get data from cell A11, A12, and A22, the formula ends up returning values in A18, A19, and A29. So they are all off by 7 rows. Here is the updated formula I am using (which is inserted into cell A4 on a sheet called "Engr":
{=IFERROR(INDEX('Roll-up'!A$9:A$87,SMALL(IF('Roll-up'!$AG$9:$AG$87="Engr",ROW('Roll-up'!$AG$9:$AG$87)-1),ROW(1:1))),"")}

Do you know why that is? Thanks so much for your help and sorry to have a follow up question.

You're welcome. The formula is similar to an INDEX MATCH formula but the key difference is that an INDEX MATCH formula returns the first match (where column A has an "X") where this one returns all of the matches.

The ROW(1:1) of the SMALL function is what changes the "k" (match number) when you drag the formula down.

Note that the range in the beginning of the INDEX function has the columns as relative (no "$", will change when dragging across) and the rows as absolute ("$", will not change).

 

[63falcondude]

I am trying to use the same formula on a different set of data, and I had a follow-up question. When I use the formula, it does not return the correct cell reference. Let's say instead of "X", I search for "OU". I get a list of of data and all of them are off by the same number of rows. For example, If I select "Engr" and I'm supposed to get data from cell A11, A12, and A22, the formula ends up returning values in A18, A19, and A29. So they are all off by 7 rows. Here is the updated formula I am using (which is inserted into cell A4 on a sheet called "Engr":
{=IFERROR(INDEX('Roll-up'!A$9:A$87,SMALL(IF('Roll-up'!$AG$9:$AG$87="Engr",ROW('Roll-up'!$AG$9:$AG$87)-1),ROW(1:1))),"")}

Do you know why that is? Thanks so much for your help and sorry to have a follow up question.

Welcome back! This is probably due to the fact that the formula assumes that the data starts in row 2. Try this instead (changes highlighted in red):

=IFERROR(INDEX('Roll-up'!A$9:A$87,SMALL(IF('Roll-up'!$AG$9:$AG$87="Engr",ROW('Roll-up'!$AG$9:$AG$87)-(ROW('Roll-up'!$AG$9)-1)),ROWS($A$1:$A1))),"") Ctrl Shift Enter

 

[lawi227]

Thank you so much! Last modification: can I include an "isblank" into the formula so that if a cell isblank, it just returns a blank, rather than a zero?

Much appreciated!! And thanks for the quick reply!

 

[63falcondude]

Thank you so much! Last modification: can I include an "isblank" into the formula so that if a cell isblank, it just returns a blank, rather than a zero?

Much appreciated!! And thanks for the quick reply!

Sure, try this one (changes to formula from post #6 in red):

=IFERROR(INDEX('Roll-up'!A$9:A$87,SMALL(IF(('Roll-up'!$AG$9:$AG$87="Engr")*('Roll-up'!A$9:A$87<>""),ROW('Roll-up'!$AG$9:$AG$87)-(ROW('Roll-up'!$AG$9)-1)),ROWS($A$1:$A1))),"") Ctrl Shift Enter

 

[lawi227]

That's perfect! I'll have to add this formula to my arsenal! Much appreciated.

Sure, try this one (changes to formula from post #6 in red):

=IFERROR(INDEX('Roll-up'!A$9:A$87,SMALL(IF(('Roll-up'!$AG$9:$AG$87="Engr")*('Roll-up'!A$9:A$87<>""),ROW('Roll-up'!$AG$9:$AG$87)-(ROW('Roll-up'!$AG$9)-1)),ROWS($A$1:$A1))),"") Ctrl Shift Enter

 

[63falcondude]

That's perfect! I'll have to add this formula to my arsenal! Much appreciated.

Happy to help!