CODEWITHSUNDEEP
excelarray-formulas
Freelancer
Freelancer

Reputation: 173

excel: filter using formulas

I have a list of people who belong to two groups (A or B). I want to create a column which lists all the people who belong to group A. I have been trying to come up with an array formula but I do not have a working example. Any help appreciated!

Names | Group | Desired Output

Bob | A | Bob

Fred | B | Eric

Matt | B | Dave

Eric | A | Fred

Dave | A |
Stew | B |
Fred | A |

Many Thanks in advance

Upvotes: 2

Views: 1049

Answers (3)

A.S.H
A.S.H

Reputation: 29332

Normal formula for column C:

C2=
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:B)/(B:B="A"),ROW()-1)), "")

Copy/paste down in column C for a sufficient number of cells.

Array formula: select a sufficient number of cells in column C and write the same formula in the formula bar then press Ctrl+Shift+Enter

Upvotes: 1

zipa
zipa

Reputation: 27869

So if your data is in range A1:B7 this would be the formula:

=IFERROR(INDEX($A$1:$A$7,SMALL(IF($B$1:$B$7="A",ROW($A$1:$A$7)-ROW($A$1)+1),ROWS($A$1:A1))),"")

This is array formula that is applied with Ctrl + Shift + Enter and you will need to drag it down.

Upvotes: 0

Nicolaesse
Nicolaesse

Reputation: 2714

From this

+---+-------+-------+
|   |   A   |   B   |
+---+-------+-------+
| 1 | Name  | Group |
| 2 | Nick  | A     |
| 3 | Marc  | A     |
| 4 | Manny | B     |
| 5 | Luck  | A     |
+---+-------+-------+

you can create two pivot tables with Name in the row label and Group in the filter data. Then you can filter only people depending on the group.

+---+-------------------+---+
|   |         A         | B |
+---+-------------------+---+
| 1 | Group             | A |
| 2 |                   |   |
| 3 | Etichette di riga |   |
| 4 | Luck              |   |
| 5 | Marc              |   |
| 6 | Nick              |   |
+---+-------------------+---+

Upvotes: 1

Related Questions