I have a Range in Excel (B3:C8) from which I want to filter out the English persons. In SQL this would be dead simple:
SELECT Persons FROM [myTable] WHERE Nationality = 'English'
How can I apply a similar filtering on a Range where the result is not a single value but a Range?
Remark: Excel has a Filter button, but all it does is HIDES the unwanted rows. I do not want hidden rows.
This is how I want my table to look like. What should the formula of G3 look like?
To get the names into a range, you could make your data a table and then create a pivot table with Nationality as the report filter and Persons as the row label. Then choose English from the nationality list. See screen shot below (ignore column D as it was not used);
Enter this in G3 and drag down. It's an array formula, so must be entered using Ctrl Shft Enter
=IFERROR(INDEX($B$3:$B$8,LARGE(($C$3:$C$8=$E$3)*(ROW($B$3:$B$8)-2),COUNTIF($C$3:$C$8,$E$3)-(ROWS($3:3)-1))),"")
Note, IfError is only available in XL 2007/10, otherwise, you'll need to use:
=IF(ISERROR(INDEX($B$3:$B$8,LARGE(($C$3:$C$8=$E$3)*(ROW($B$3:$B$8)-2),COUNTIF($C$3:$C$8,$E$3)-(ROWS($3:3)-1)))),"",INDEX($B$3:$B$8,LARGE(($C$3:$C$8=$E$3)*(ROW($B$3:$B$8)-2),COUNTIF($C$3:$C$8,$E$3)-(ROWS($3:3)-1))))
This version will work in any version of Excel and gives the results in the order listed
In G3:
=IF(ROWS(G$3:G3)>COUNTIF(C$3:C$8,E$3),"",INDEX(B$3:B$8,SMALL(IF(C$3:C$8=E$3,ROW(C$3:C$8)-ROW(C$3)+1),ROWS(G$3:G3))))
confirmed with CTRL+SHIFT+ENTER (pressed together) and copied down as far as required
One way would be to use this IF statement =IF($E$3=C3,B3,"") which would give you the persons name if the Nationality matches the filter or a blank if it does not.
=If($E$3=English,B3,"")
You can make this more generalized by doing:
=If($E$3=C3,B3,"")
