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!
|