Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering without filtering
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering without filtering
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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering without filtering
On Aug 26, 11:20*pm, "T. Valko" wrote:
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 wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering without filtering
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP wrote in message ... On Aug 26, 11:20 pm, "T. Valko" wrote: 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 wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering | Excel Worksheet Functions | |||
filtering | Excel Discussion (Misc queries) | |||
Filtering | Excel Discussion (Misc queries) | |||
Filtering | Excel Discussion (Misc queries) | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions |