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!

 

[lawi227]

Sorry.. but I caught an error on my behalf. It has to do with the formatting of my table I believe. One last quick help hopefully..

In column H1:K1, I have a merged cell with a title in it. Below that merged cell are 4 questions (one question in H1, one question in I1, J1, and K1). Starting in row 3, I put an "X" below each question that is correct.

The formula I was using does not always pull over the correct "X". I cannot identify a pattern.

It also appears that Columns W:AD (which do not have a merged title in row 1), also do not return the correct "X". This section of my table also has questions and I input an "X" if they apply.

All the other columns appear to be working but it's only where there is a merged cell and information in row 1, does the formula not work.

Is there perhaps a better formula to use for those cells? Note: these cells have blanks and thats why I was asking about removing the blanks so they don't show as "0".

Thanks once again.

Happy to help!

 

[63falcondude]

Merged cells lead to problems when using formulas. I tend to use Format Cells > Alignment > Horizontal: Center Across Selection instead.

That being said, it seems that your layout is different from what you explained originally. Up to this point, the formula was only looking for an "X" in one column. Is this no longer the case?

I think that it will be best if you can create a small sample of your data along with the expected results of the formula.

 

[lawi227]

Now I am searching one column (AG) for one value, and returning all the data. Basically it is a dynamic filter (i.e., If I want to see all the data in one tab where Column AG has a "Engr". I want to be able to drag that formula that you created above from column A all the way across to column AH.

Here is a sample set of data (I labeled the column letters, and denoted where cell A1 is):

[TABLE="class: grid, width: 2497"]
<colgroup><col><col><col><col><col><col><col><col span="7"><col span="2"><col><col><col><col><col><col><col><col><col span="2"><col span="2"><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[TD]Column C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD="colspan: 4"]H/I/J/K[/TD]
[TD="colspan: 3"]L/M/N[/TD]
[TD="colspan: 2"]O/P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[/TR]
[TR]
[TD] A1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Compliance[/TD]
[TD="colspan: 3"]Resources[/TD]
[TD="colspan: 2"]Awareness[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Focus #[/TD]
[TD]Element[/TD]
[TD]Focus[/TD]
[TD]Assessment Plan/Criteria[/TD]
[TD][/TD]
[TD]Summary Execution Findings[/TD]
[TD]Execution Findings?[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[TD]Q7[/TD]
[TD]Q8[/TD]
[TD]Q9[/TD]
[TD][/TD]
[TD]Summary Spirit/Intent Findings [/TD]
[TD]Spirit/Intent Findings?[/TD]
[TD][/TD]
[TD]Summary Design Findings[/TD]
[TD]Design Findings?[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[TD]Q7[/TD]
[TD]Q8[/TD]
[TD][/TD]
[TD]Gap(s)[/TD]
[TD]Team [/TD]
[TD]Place[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Fundamentals[/TD]
[TD]Leadership [/TD]
[TD]Observation: [/TD]
[TD][/TD]
[TD]text[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]None[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]None[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]sample text[/TD]
[TD]OU[/TD]
[TD]name 1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Fundamentals[/TD]
[TD]Preservation[/TD]
[TD]Interview: [/TD]
[TD][/TD]
[TD]text [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]None[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]None[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]sample text[/TD]
[TD]Engr[/TD]
[TD]name 2[/TD]
[/TR]
</tbody>[/TABLE]

In this simplistic view (with sample, dummy data and text), I would want on a separate worksheet to return all rows that has "ENGR" in column AG. In this example, that would only return row 4. I want it to by dynamic so there are no gaps, and so "0" show up as blanks (as you can see there are a lot of blanks). I thought I could leverage the formula you created and just change the data that I am indexing, but that didn't work for the columns with (Q1, Q2, etc.).

PS.. How did you get to copy and paste your worksheet in post #2 so neatly?

Merged cells lead to problems when using formulas. I tend to use Format Cells > Alignment > Horizontal: Center Across Selection instead.

That being said, it seems that your layout is different from what you explained originally. Up to this point, the formula was only looking for an "X" in one column. Is this no longer the case?

I think that it will be best if you can create a small sample of your data along with the expected results of the formula.

 

[63falcondude]

Based off of your sample in post #13, this should do the trick. The formula is to be entered in A3 of the separate worksheet then dragged to AH3 and down as far as needed.

=IFERROR(IF(INDEX(Sheet1!A$3:A$4,SMALL(IF(Sheet1!$AG$3:$AG$4="Engr",ROW(Sheet1!$A$3:$A$4)-(ROW(Sheet1!$A$3)-1)),ROWS($A$1:$A1)))=0,"",INDEX(Sheet1!A$3:A$4,SMALL(IF(Sheet1!$AG$3:$AG$4="Engr",ROW(Sheet1!$A$3:$A$4)-(ROW(Sheet1!$A$3)-1)),ROWS($A$1:$A1)))),"") Ctrl Shift Enter

PS.. How did you get to copy and paste your worksheet in post #2 so neatly?

I use the MrExcel HTML Maker
See here: https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[lawi227]

I replaced 'Sheet 1!' with 'Roll-up'! (because that is the worksheet that contains the data I'm trying to pull and it didn't pull anything after I hit Ctrl Shift Enter.

Here is the formula:
=IFERROR(IF(INDEX('Roll-up'!A$3:A$4,SMALL(IF('Roll-up'!$AG$3:$AG$4="Engr",ROW('Roll-up'!$A$3:$A$4)-(ROW('Roll-up'!$A$3)-1)),ROWS($A$1:$A1)))=0,"",INDEX('Roll-up'!A$3:A$4,SMALL(IF('Roll-up'!$AG$3:$AG$4="Engr",ROW('Roll-up'!$A$3:$A$4)-(ROW('Roll-up'!$A$3)-1)),ROWS($A$1:$A1)))),"")

Any thoughts? Did I not link the right ranges to the right worksheet?

EDIT: I see the range needs to be adjusted to capture all the data through row 80.

Based off of your sample in post #13, this should do the trick. The formula is to be entered in A3 of the separate worksheet then dragged to AH3 and down as far as needed.

=IFERROR(IF(INDEX(Sheet1!A$3:A$4,SMALL(IF(Sheet1!$AG$3:$AG$4="Engr",ROW(Sheet1!$A$3:$A$4)-(ROW(Sheet1!$A$3)-1)),ROWS($A$1:$A1)))=0,"",INDEX(Sheet1!A$3:A$4,SMALL(IF(Sheet1!$AG$3:$AG$4="Engr",ROW(Sheet1!$A$3:$A$4)-(ROW(Sheet1!$A$3)-1)),ROWS($A$1:$A1)))),"") Ctrl Shift Enter



I use the MrExcel HTML Maker
See here: https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[63falcondude]

I replaced 'Sheet 1!' with 'Roll-up'! (because that is the worksheet that contains the data I'm trying to pull and it didn't pull anything after I hit Ctrl Shift Enter.

Here is the formula:
=IFERROR(IF(INDEX('Roll-up'!A$3:A$4,SMALL(IF('Roll-up'!$AG$3:$AG$4="Engr",ROW('Roll-up'!$A$3:$A$4)-(ROW('Roll-up'!$A$3)-1)),ROWS($A$1:$A1)))=0,"",INDEX('Roll-up'!A$3:A$4,SMALL(IF('Roll-up'!$AG$3:$AG$4="Engr",ROW('Roll-up'!$A$3:$A$4)-(ROW('Roll-up'!$A$3)-1)),ROWS($A$1:$A1)))),"")

Any thoughts? Did I not link the right ranges to the right worksheet?

I based that formula off of the example that you posted in post #13. Is that the data that you tested the formula on? You would want to test the formula given on the sample that you gave me and get that working before moving onto your main sheet.

When you are ready to use the formula on your main sheet, you would have to adjust the ranges to fit your data (i.e. change the 4's to the last row of your data).

 

[lawi227]

I got it to work. Thanks so much for bearing with me and helping me. Much appreciated!

 

[63falcondude]

I got it to work. Thanks so much for bearing with me and helping me. Much appreciated!

Great, glad to help.