3

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?

enter image description here

Improve this question

5 Answers 5

Reset to default
3

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);

Pivot Table

Improve this answer
4
  • You don't need to create a pivot table to filter on the Nationality. A simple filter of the Nationality column would have suffice and he mentioned he didn't actually want to hide rows(filtering), he wants to delete them completely.
    – Jay
    Dec 29, 2011 at 22:05
  • 1
    @Jay I don't see the words "delete" in the question. Also, a simple filter does hide rows. Lastely, there may be many ways of accomplishing the same thing when asked; 'This is how I want my table to look like. What should the formula of G3 look like?'
    – CharlieRB
    Dec 29, 2011 at 23:20
  • I think a pivot table is a good solution here. +1
    – Doug Glancy
    Dec 29, 2011 at 23:48
  • In the question, he said, "Remark: Excel has a Filter button, but all it does is HIDES the unwanted rows. I do not want hidden rows." Reading between the lines, what he wants is to have a column with the names that fit the criteria and to have the other names not exist in that column. And don't get me wrong, pivot tables are great but I think for such a simple table, it's not worth making a pivot table. On another note, Doug Glancy's answers exactly what is being asked.
    – Jay
    Dec 30, 2011 at 14:13
3

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))))
Improve this answer
2
  • Doug, I wanted exactly something like that. Thank you for the great effort, it works perfectly. However, I still mark the pivot table as solution. The reason is maintainability. This formula is very complex already for our textbook example. When applying in real life scenario, its complexity explodes further. Pivot table is not as perfect as this, but very simple to use. Thank you again!
    – user24752
    Jan 1, 2012 at 17:08
  • I agree wholeheartedly. I like to figure out formulas like this, but I seldom end up using the complex ones. In the vast jumbled landscape that is Excel, pivot tables are a rock of stability.
    – Doug Glancy
    Jan 1, 2012 at 20:30
2

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

Improve this answer
3
  • There is an error in the formula. If I turn E3 to 'German', John stays in G3 instead of Hans.The same with selecting 'French', Luis does not show up, but John takes his place.
    – user24752
    Jan 6, 2012 at 19:46
  • Works fine for me.....did you confirm with CTRL+SHIFT+ENTER and then copy down? If I change E3 to German I see Hans and Jurgen as expected.....
    – barry houdini
    Jan 6, 2012 at 20:31
  • Sorry, my fault! You are right.
    – user24752
    Jan 7, 2012 at 20:36
0

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.

Improve this answer
0

=If($E$3=English,B3,"")

You can make this more generalized by doing:

=If($E$3=C3,B3,"")

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .