Extracting all rows based on a value of cell witho

问题:

I'm really struggling to find an answer to this as online I've really only found VBA solutions to this problem which isn't what I wish to learn how to do.

THE PROBLEM

BLOOD NAME  AGE GENDER
A   David   18  Male
B   Sarah   22  Female
O   Lucy    32  Female
AB  Steven  23  Male
O   John    11  Male
B   Mike    25  Male
AB  Paul    24  Male
O   Amy 23  Female
B   Drake   22  Female
O   Linda   11  Female

Very simply from the above dataset I wish to recreate this range but filter for only select BLOOD TYPE O.

MY ATTEMPTS

Started with a VLookup table however that stops at the first occurrence of O. Then tried incorporating IF/THEN/ELSE logic into a MATCH operand trying to locate the row numbers outputting to an array. (not gonna post my failed attempts) I did find a similarish problem online however they solved it via referencing the range manually using ROW(A1), ROW(A2) etc etc wasn't what I after.

Really want to learn how to do this type of iterative selections using Excel formulae only. Even if not solving the problem any direction towards resources where I can learn more about this type problem, would be still appreciated.

回答1:

This does not use array formulas, but does use a helper column. Assuming data in cols A through D, in E2 enter:

=IF(A2="O",1+MAX($E$1:E1),"")

and copy down:

Each of the O rows is marked with a simple sequential value. This makes it easy for the usual MATCH() / INDEX() methods.

Pick some other cell and enter:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),$E:$E,0)),"")

and copy this cell both across and down:

回答2:

Here is a solution with array formulas. It will calculate extremely slowly, and honestly VBA is a much better solution. You will need to tell excel these are array formulas by hitting "Ctrl + Shift + Enter" after inputting the formulas, this will add the {} around the equation. Finally, drag down the array formulas to see the first "X" results with blood type "O":

First cell formula for "Blood" --> assumes blood is in column A of sheet1 {=IFERROR(INDEX(Sheet1!$A:$D,SMALL(IF(Sheet1!$A:$A="O",ROW(Sheet1!$A:$A)),ROW(1:1)),1,1),"")}

First cell formula for "Name" --> assumes name is in column B of sheet1 {=IFERROR(INDEX(Sheet1!$A:$D,SMALL(IF(Sheet1!$A:$A="O",ROW(Sheet1!$A:$A)),ROW(1:1)),2,1),"")}

First cell formula for "Age" --> assumes age is in column c of sheet1 {=IFERROR(INDEX(Sheet1!$A:$D,SMALL(IF(Sheet1!$A:$A="O",ROW(Sheet1!$A:$A)),ROW(1:1)),3,1),"")}

First cell formula for "Gender" --> assumes gender is in column d of sheet1 {=IFERROR(INDEX(Sheet1!$A:$D,SMALL(IF(Sheet1!$A:$A="O",ROW(Sheet1!$A:$A)),ROW(1:1)),4,1),"")}

Results:

BLOOD   NAME    AGE GENDER
O       Lucy    32  Female
O       John    11  Male
O       Amy     23  Female
O       Linda   11  Female

回答3:

The following array formula can be put in row 2 (anywhere from column E onward) and copied across 3 columns and down as far as is necessary:

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("O",$A$2:$A$11)),ROW($A$2:$A$11),""),ROW()-1)),"")

This is entered using Ctrl + Shift + Enter and uses a fixed array (A2:A11). If your array is going to change size, you can make the reference to it dynamic by using INDIRECT and COUNTA so that it always encompasses the used range, like so:

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("O",INDIRECT("$A2:$A"&COUNTA(A:A)))),ROW(INDIRECT("$A2:$A"&COUNTA(A:A))),""),ROW()-1)),"")

What is happening:

The SEARCH function is looking for "O"s, then the IF returns the row number if an "O" was found and nothing if no "O" was found.

The SMALL function is looking for the nth instance of the results returned by the SEARCH function, where n = ROW()-1.

The INDEX function returns the nth value from the array A:A, B:B, etc, where n = the row number returned by the SMALL function.

The IFERROR function is not necessary but it makes for a cleaner dataset, all it does is replace the formulas that didn't return anything useful with a blank instead.