Vlookup for repitative data

  • Thread starter Thread starter Raju
  • Start date Start date
R

Raju

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.
 
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
 
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
 
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
Click to expand...

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,

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 :




- Show quoted text -
Click to expand...

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!
 
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!
Click to expand...

Its really cool !!!

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