Filtering without filtering

[hooroy63]

I’m stumped. Is there a way to extract data from a list without using
the filter feature, and without using macros? I want to do this
strictly by using formulas. For example, in the sheet below, I would
like a formula in cell A16 that looks at the data in cells A2:B12 and
pulls in the first occurrence of a name in the East region, then a
formula in A17 that pulls in the second occurrence of a name in the
East region, and so on. When all is done, cells A16:C18, each with
their own formula, would appear as shown below. NOTE: I’d also prefer
not to juxtapose the “Name” and “Region” columns.

I’ve played around with VLOOKUP, LOOKUP, INDEX, MATCH, INDEX/MATCH
combo, but I can’t seem to get the second and third occurrences.

Using the following formula in cell A16 yields “Sal”, as expected, but
what can I use for A17 and A18?
A16: =INDEX(A3:A12,MATCH(A15,B3:B12,0))

I need to do this because the “Regions” in col. B are calculated
elsewhere and automatically change frequently. I want cells A16:C18 to
update automatically via formulas so the user doesn’t have to bother
with filters. Although a simple VBA macro would be the easy way to do
this (“FOR EACH cell . . .”), unfortunately macros are forbidden in
our sheets.

Thanks for any help you can offer.


A B C
1 <DATA>
2 Name Region
3 Ron Central
4 Bill West
5 Sal East
6 Chuck Central
7 Jim
8 Dick West
9 Joe East
10 Allan Central
11 David West
12 Jim East
13
14 <GROUP LISTING>
15 East Central West
16 Sal Ron Bill
17 Joe Chuck Dick
18 Jim Allan David

 

[T. Valko]

Try this:

Enter this array formula** in A16:

=IF(ROWS(A$16:A16)<=COUNTIF($B$3:$B$12,A$15),INDEX($A$3:$A$12,SMALL(IF($B$3:$B$12=A$15,ROW(A$3:A$12)),ROWS(A$16:A16))-ROW(A$3)+1),"")

Copy across to C16 then down until you get a solid row of blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


I’m stumped. Is there a way to extract data from a list without using
the filter feature, and without using macros? I want to do this
strictly by using formulas. For example, in the sheet below, I would
like a formula in cell A16 that looks at the data in cells A2:B12 and
pulls in the first occurrence of a name in the East region, then a
formula in A17 that pulls in the second occurrence of a name in the
East region, and so on. When all is done, cells A16:C18, each with
their own formula, would appear as shown below. NOTE: I’d also prefer
not to juxtapose the “Name” and “Region” columns.

I’ve played around with VLOOKUP, LOOKUP, INDEX, MATCH, INDEX/MATCH
combo, but I can’t seem to get the second and third occurrences.

Using the following formula in cell A16 yields “Sal”, as expected, but
what can I use for A17 and A18?
A16: =INDEX(A3:A12,MATCH(A15,B3:B12,0))

I need to do this because the “Regions” in col. B are calculated
elsewhere and automatically change frequently. I want cells A16:C18 to
update automatically via formulas so the user doesn’t have to bother
with filters. Although a simple VBA macro would be the easy way to do
this (“FOR EACH cell . . .”), unfortunately macros are forbidden in
our sheets.

Thanks for any help you can offer.


A B C
1 <DATA>
2 Name Region
3 Ron Central
4 Bill West
5 Sal East
6 Chuck Central
7 Jim
8 Dick West
9 Joe East
10 Allan Central
11 David West
12 Jim East
13
14 <GROUP LISTING>
15 East Central West
16 Sal Ron Bill
17 Joe Chuck Dick
18 Jim Allan David

 

[hooroy63]

Try this:

Enter this array formula** in A16:

=IF(ROWS(A$16:A16)<=COUNTIF($B$3:$B$12,A$15),INDEX($A$3:$A$12,SMALL(IF($B$3­:$B$12=A$15,ROW(A$3:A$12)),ROWS(A$16:A16))-ROW(A$3)+1),"")

Copy across to C16 then down until you get a solid row of blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


I’m stumped. Is there a way to extract data from a list without using
the filter feature, and without using macros? I want to do this
strictly by using formulas. For example, in the sheet below, I would
like a formula in cell A16 that looks at the data in cells A2:B12 and
pulls in the first occurrence of a name in the East region, then a
formula in A17 that pulls in the second occurrence of a name in the
East region, and so on. When all is done, cells A16:C18, each with
their own formula, would appear as shown below. NOTE: I’d also prefer
not to juxtapose the “Name” and “Region” columns.

I’ve played around with VLOOKUP, LOOKUP, INDEX, MATCH, INDEX/MATCH
combo, but I can’t seem to get the second and third occurrences.

Using the following formula in cell A16 yields “Sal”, as expected, but
what can I use for A17 and A18?
A16:    =INDEX(A3:A12,MATCH(A15,B3:B12,0))

I need to do this because the “Regions” in col. B are calculated
elsewhere and automatically change frequently. I want cells A16:C18 to
update automatically via formulas so the user doesn’t have to bother
with filters. Although a simple VBA macro would be the easy way to do
this (“FOR EACH cell . . .”), unfortunately macros are forbidden in
our sheets.

Thanks for any help you can offer.

  A B C
1 <DATA>
2 Name Region
3 Ron Central
4 Bill West
5 Sal East
6 Chuck Central
7 Jim
8 Dick West
9 Joe East
10 Allan Central
11 David West
12 Jim East
13
14   <GROUP LISTING>
15 East Central West
16 Sal Ron Bill
17 Joe Chuck Dick
18 Jim Allan David

Biff,
Brilliant -- works like a charm! Thanks for this and for all you do
for us wannabe's.
hooroy

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try this:

Enter this array formula** in A16:

=IF(ROWS(A$16:A16)<=COUNTIF($B$3:$B$12,A$15),INDEX($A$3:$A$12,SMALL(IF($B$3­:$B$12=A$15,ROW(A$3:A$12)),ROWS(A$16:A16))-ROW(A$3)+1),"")

Copy across to C16 then down until you get a solid row of blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


I’m stumped. Is there a way to extract data from a list without using
the filter feature, and without using macros? I want to do this
strictly by using formulas. For example, in the sheet below, I would
like a formula in cell A16 that looks at the data in cells A2:B12 and
pulls in the first occurrence of a name in the East region, then a
formula in A17 that pulls in the second occurrence of a name in the
East region, and so on. When all is done, cells A16:C18, each with
their own formula, would appear as shown below. NOTE: I’d also prefer
not to juxtapose the “Name” and “Region” columns.

I’ve played around with VLOOKUP, LOOKUP, INDEX, MATCH, INDEX/MATCH
combo, but I can’t seem to get the second and third occurrences.

Using the following formula in cell A16 yields “Sal”, as expected, but
what can I use for A17 and A18?
A16: =INDEX(A3:A12,MATCH(A15,B3:B12,0))

I need to do this because the “Regions” in col. B are calculated
elsewhere and automatically change frequently. I want cells A16:C18 to
update automatically via formulas so the user doesn’t have to bother
with filters. Although a simple VBA macro would be the easy way to do
this (“FOR EACH cell . . .”), unfortunately macros are forbidden in
our sheets.

Thanks for any help you can offer.

A B C
1 <DATA>
2 Name Region
3 Ron Central
4 Bill West
5 Sal East
6 Chuck Central
7 Jim
8 Dick West
9 Joe East
10 Allan Central
11 David West
12 Jim East
13
14 <GROUP LISTING>
15 East Central West
16 Sal Ron Bill
17 Joe Chuck Dick
18 Jim Allan David

Biff,
Brilliant -- works like a charm! Thanks for this and for all you do
for us wannabe's.
hooroy