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!

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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!
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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!
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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!
>> >>
>> >>
>> >>
>>
>>
>>