Hi Frndz,

I have thousands of data as mentioned below

Product ID
Abc 101
Abc 100
Abb 105
Abb 107
Abc 102
Abb 109

I am looking for a formula which will gather all list of ID associated
with Product name from list

I can do this by using advance filter as well but I need some fixed
formula which I dont want to alter or perform any activity every time
when data changes.

hi,

you could use a pivot table

--
isabelle

On Oct 5, 9:06*am, isabelle wrote:
hi,

you could use a pivot table

--
isabelle

Actually I need a formula
Is there any such formula??

How many different IDs are you likely to have for each product? Your
example data shows three for Abc and 3 for Abb, but is this
representative?

Also, can you describe how you would like to see the output? Do you
want to see something like this:

Abc 101 100 102
Abb 105 107 109

where those IDs are in separate columns, or like this:

Abc 101, 100, 102
Abb 105, 107, 109

where the IDs are in one cell, each separated by a comma (plus maybe a
space), or like this:

Abc 101
100
102
Abb 105
107
109

(might be slightly mis-aligned) where each product name appears once
and the IDs are listed on separate rows in the same column ??

Hope this helps.

Pete


On Oct 7, 2:09*pm, Raju wrote:
On Oct 5, 9:06*am, isabelle wrote:

hi,

you could use a pivot table

--
isabelle

Actually I need a formula
Is there any such formula??

hi,

select range E1:E4
copy this formula in E1

=INDEX(ID,SMALL(IF(Product="Abc",ROW(INDIRECT("1:" &ROWS(Product)))),ROW()))

validate with ctrl + shift + enter

--
isabelle


Le 2011-10-07 09:09, Raju a écrit :

Actually I need a formula
Is there any such formula??

On Oct 7, 9:57*am, isabelle wrote:
hi,

select range E1:E4
copy this formula in E1

=INDEX(ID,SMALL(IF(Product="Abc",ROW(INDIRECT("1:" &ROWS(Product)))),ROW()))

validate with ctrl + shift + enter

--
isabelle

Le 2011-10-07 09:09, Raju a écrit : Actually I need a formula
Is there any such formula??

Hey isabelle,

Its really nice one!!!
An array formula I had used few of them but in this one I didn't
understand logic for "ROW(INDIRECT("1:"&ROWS(Product))" what its
actually returning to small function when if condition gets true i.e.
Product="Abc" ??
That would be great if you could tell me the logic

When I enterted =INDIRECT("1:"&ROWS(Product)) into different cell say
"D1" for tesing output what it retuns, it shown "product".

On Oct 7, 9:57*am, isabelle wrote:
hi,

select range E1:E4
copy this formula in E1

=INDEX(ID,SMALL(IF(Product="Abc",ROW(INDIRECT("1:" &ROWS(Product)))),ROW()))

validate with ctrl + shift + enter

--
isabelle

Le 2011-10-07 09:09, Raju a écrit :



Actually I need a formula
Is there any such formula??- Hide quoted text -

- Show quoted text -

Hey isabelle,

Its really nice one!!!
An array formula I had used few of them but in this one I didn't
understand logic for "ROW(INDIRECT("1:"&ROWS(Product))" what its
actually returning to small function when if condition gets true i.e.
Product="Abc" ??
That would be great if you could tell me the logic

When I enterted =INDIRECT("1:"&ROWS(Product)) into different cell say
"D1" for tesing output what it retuns, it shown "product".

hi,

I'll try to explain the way I understand

Product ID
Abc 101
Abc 100
Abb 105
Abb 107
Abc 102
Abb 109


=INDEX(ID,SMALL(IF(Product="Abc",ROW(INDIRECT("1:" &ROWS(Product)))),ROW()))

Product="Abc" evaluates each cell in the range and returns TRUE or FALSE as an internal array
True
True
False
False
True
False

ROW(INDIRECT("1:"&ROWS(Product))) returns an array containing the line number

IF(Product="Abc",ROW(INDIRECT("1:"&ROWS(Product)))
then we check each value of internal array "true or false" to return the line number
note that we start at line 1 for the INDEX function

now the internal array is:

2
3
0
0
6
0

SMALL(internal array,ROW())
Return a new internal array :

2
3
6
0
0
0

=INDEX(ID,internal array)
Return:

101
100
102
#NUM!
#NUM!
#NUM!


--
isabelle

On Oct 10, 11:23*am, isabelle wrote:
hi,

I'll try to explain the way I understand

Product ID
Abc 101
Abc 100
Abb 105
Abb 107
Abc 102
Abb 109

* =INDEX(ID,SMALL(IF(Product="Abc",ROW(INDIRECT("1:" &ROWS(Product)))),ROW()))

Product="Abc" evaluates each cell in the range and returns TRUE or FALSE as an internal array
True
True
False
False
True
False

* ROW(INDIRECT("1:"&ROWS(Product))) returns an array containing the line number

* IF(Product="Abc",ROW(INDIRECT("1:"&ROWS(Product)))
then we check each value of internal array "true or false" to return the line number
note that we start at line 1 for the INDEX function

now the internal array is:

2
3
0
0
6
0

SMALL(internal array,ROW())
Return a new internal array :

2
3
6
0
0
0

=INDEX(ID,internal array)
Return:

101
100
102
#NUM!
#NUM!
#NUM!

--
isabelle

Its really cool !!!

it more clear now to me, I think I can use any type of array formula
henceforth
thanks a lot!!!
isabelle