| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a list based on single shared criteria
An array or function is needed to create a list based on a single shared
criteria. ID# Value Name y1 5 Jill x2 7 Jack x3 3 Mary y1 15 James y1 9 Vito z1 4 Wally Based on the above table a list selecting ID# y1 would generate: Jill James Vito Thanks in advance! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create a list based on single shared criteria
Hi!
Try using a filter........ ....but, if you want to make this dynamic and use a formula........ Assume you enter the criteria in a cell, say, D1: D1 = y1 Entered as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(A$2:A$7,D$1),INDEX(C$2:C$7 ,SMALL(IF(A$2:A$7=D$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1))),"") Copy down until you get blanks. Biff "David127" wrote in message ... An array or function is needed to create a list based on a single shared criteria. ID# Value Name y1 5 Jill x2 7 Jack x3 3 Mary y1 15 James y1 9 Vito z1 4 Wally Based on the above table a list selecting ID# y1 would generate: Jill James Vito Thanks in advance! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create a list based on single shared criteria
Hi Biff-
I tested out your array with my example & it worked like a charm! Howerver, I expanded the array to cover a much larger range & I'm getting the #NUM!. Below are the two array's for comparison (your's is altered to reflect similar column's, worksheets etc). Your example: =IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"") Mine: =IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"") Using the Formula Palet the "Small" function in my example does not return a value, just a blank. Have I exceeded its limit? If so are there any options to your array. Many Thanks! "Biff" wrote: Hi! Try using a filter........ ....but, if you want to make this dynamic and use a formula........ Assume you enter the criteria in a cell, say, D1: D1 = y1 Entered as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(A$2:A$7,D$1),INDEX(C$2:C$7 ,SMALL(IF(A$2:A$7=D$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1))),"") Copy down until you get blanks. Biff "David127" wrote in message ... An array or function is needed to create a list based on a single shared criteria. ID# Value Name y1 5 Jill x2 7 Jack x3 3 Mary y1 15 James y1 9 Vito z1 4 Wally Based on the above table a list selecting ID# y1 would generate: Jill James Vito Thanks in advance! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create a list based on single shared criteria
Hi!
One question: Is the range in Identifier column A, A2:A777, or A2:A776? You're using A2:A777 in the Countif function and A2:A776 everywhere else? They all need to be the same. Try this (using 2:776 in all ranges) =IF(ROWS($1:1)<=COUNTIF(Identifier!A$2:A$776,E$2), INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$2 :A$776=E$2,ROW(A$2:A$776)-ROW(A$2)+1),ROWS($1:1))),"") Biff "David127" wrote in message ... Hi Biff- I tested out your array with my example & it worked like a charm! Howerver, I expanded the array to cover a much larger range & I'm getting the #NUM!. Below are the two array's for comparison (your's is altered to reflect similar column's, worksheets etc). Your example: =IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"") Mine: =IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"") Using the Formula Palet the "Small" function in my example does not return a value, just a blank. Have I exceeded its limit? If so are there any options to your array. Many Thanks! "Biff" wrote: Hi! Try using a filter........ ....but, if you want to make this dynamic and use a formula........ Assume you enter the criteria in a cell, say, D1: D1 = y1 Entered as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(A$2:A$7,D$1),INDEX(C$2:C$7 ,SMALL(IF(A$2:A$7=D$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1))),"") Copy down until you get blanks. Biff "David127" wrote in message ... An array or function is needed to create a list based on a single shared criteria. ID# Value Name y1 5 Jill x2 7 Jack x3 3 Mary y1 15 James y1 9 Vito z1 4 Wally Based on the above table a list selecting ID# y1 would generate: Jill James Vito Thanks in advance! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create a list based on single shared criteria
My appologies... I was trying multiple variations & copied both example
array's incorrectly. The range is indeed A2:A776 & even when its the same I get a error #N/A. However when I make the range A2:A339 I get the appropriate rusult. If I bring the range to A2:A340 or higher the SMALL function does not return a value. "Biff" wrote: Hi! One question: Is the range in Identifier column A, A2:A777, or A2:A776? You're using A2:A777 in the Countif function and A2:A776 everywhere else? They all need to be the same. Try this (using 2:776 in all ranges) =IF(ROWS($1:1)<=COUNTIF(Identifier!A$2:A$776,E$2), INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$2 :A$776=E$2,ROW(A$2:A$776)-ROW(A$2)+1),ROWS($1:1))),"") Biff "David127" wrote in message ... Hi Biff- I tested out your array with my example & it worked like a charm! Howerver, I expanded the array to cover a much larger range & I'm getting the #NUM!. Below are the two array's for comparison (your's is altered to reflect similar column's, worksheets etc). Your example: =IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"") Mine: =IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"") Using the Formula Palet the "Small" function in my example does not return a value, just a blank. Have I exceeded its limit? If so are there any options to your array. Many Thanks! "Biff" wrote: Hi! Try using a filter........ ....but, if you want to make this dynamic and use a formula........ Assume you enter the criteria in a cell, say, D1: D1 = y1 Entered as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(A$2:A$7,D$1),INDEX(C$2:C$7 ,SMALL(IF(A$2:A$7=D$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1))),"") Copy down until you get blanks. Biff "David127" wrote in message ... An array or function is needed to create a list based on a single shared criteria. ID# Value Name y1 5 Jill x2 7 Jack x3 3 Mary y1 15 James y1 9 Vito z1 4 Wally Based on the above table a list selecting ID# y1 would generate: Jill James Vito Thanks in advance! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create a list based on single shared criteria
Hi!
I don't know why you're having a problem. Can I see your file? I don't need the whole thing, just the sheet that contains the data you're try to extract. If so, I'm at: xlcanhelpatcomcastperiodnet Remove CAN and replace AT and PERIOD with the obvious. Biff "David127" wrote in message ... My appologies... I was trying multiple variations & copied both example array's incorrectly. The range is indeed A2:A776 & even when its the same I get a error #N/A. However when I make the range A2:A339 I get the appropriate rusult. If I bring the range to A2:A340 or higher the SMALL function does not return a value. "Biff" wrote: Hi! One question: Is the range in Identifier column A, A2:A777, or A2:A776? You're using A2:A777 in the Countif function and A2:A776 everywhere else? They all need to be the same. Try this (using 2:776 in all ranges) =IF(ROWS($1:1)<=COUNTIF(Identifier!A$2:A$776,E$2), INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$2 :A$776=E$2,ROW(A$2:A$776)-ROW(A$2)+1),ROWS($1:1))),"") Biff "David127" wrote in message ... Hi Biff- I tested out your array with my example & it worked like a charm! Howerver, I expanded the array to cover a much larger range & I'm getting the #NUM!. Below are the two array's for comparison (your's is altered to reflect similar column's, worksheets etc). Your example: =IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"") Mine: =IF(ROWS($2:10)<=COUNTIF(Identifier!A$2:A$777,E$2) ,INDEX(Identifier!C$2:C$776,SMALL(IF(Identifier!A$ 2:A$776=E$2,ROW(Identifier!A$2:A$776)-ROW(Identifier!A$2)+1),ROWS($2:10))),"") Using the Formula Palet the "Small" function in my example does not return a value, just a blank. Have I exceeded its limit? If so are there any options to your array. Many Thanks! "Biff" wrote: Hi! Try using a filter........ ....but, if you want to make this dynamic and use a formula........ Assume you enter the criteria in a cell, say, D1: D1 = y1 Entered as an array using the key combo of CTRL,SHIFT,ENTER: =IF(ROWS($1:1)<=COUNTIF(A$2:A$7,D$1),INDEX(C$2:C$7 ,SMALL(IF(A$2:A$7=D$1,ROW(A$2:A$7)-ROW(A$2)+1),ROWS($1:1))),"") Copy down until you get blanks. Biff "David127" wrote in message ... An array or function is needed to create a list based on a single shared criteria. ID# Value Name y1 5 Jill x2 7 Jack x3 3 Mary y1 15 James y1 9 Vito z1 4 Wally Based on the above table a list selecting ID# y1 would generate: Jill James Vito Thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
| How do I get LIST on the DATA menu bar-I need Create List | New Users to Excel | |||
| create a drop down list with the source from a different workbook | Excel Discussion (Misc queries) | |||
| How do you create a drop down list? | Excel Discussion (Misc queries) | |||
| Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
Linear Mode
