Vlookup

[Guest]

hi,
i hv 3 columns - a, b, & c
in col a, i hv some identity nos and in col b, i hv related attributes
(Text) corresponding to each ID. now it happens that one ID may hv more then
1 attribute related to it nd so the IDs in col 'a' may be repeated.
i want to know all the attributes for an ID in a separate column with no
repetetion of IDs. how wud i do that..thx in advance for solutions.

 

[Guest]

A little hard to understand as its in text speak, but he lookup you qould
require (provide you called the table "test " array).

=vlookup(A2,test,2,false)
Will return the value of column B for the matching value in A2

Vlookup is not tolerant of multiple rows with the same ID number, maybe if
you provided a rough exmaple we could help consolidate your list to have
unique IDs and still function correctly.

 

[Guest]

thx paul,

may be some other way and not vlookup.....
let me giv u an ex:

a (IDs) b (Corresponding character)
xyz x1
yyz y1
zzx z1
xyz y1
xyz z1
xxyyzz z2
xxx x3
yyz z3

result should be:
a b c d
xyz x1 y1 z1
yyz y1 z3
zzx z1
xxyyzz z2
xxx x3

notice the rows got reduced as there was repetition in the IDs.
hope u got the idea...thx..

 

[Guest]

That helps greatly, depending on what the final use is I would consider
putting them inot a pivot table, select the table of data, choose INSERT >
Pivot Table and then select the data, it should then group it in the
following fashion:-

Row Labels
xxx
x3
xxyyzz
z2
xyz
x1
y1
z1
yyz
y1
z3
zzx
z1
(blank)
(blank)
Grand Total

 

[Guest]

paul,

i hv around 10000 IDs and the repetition for 1 ID may not be more than 5
times. and unfortunately it outnumbers the pivot capacity.

 

[Guest]

A bit of a different way to do it is to again use a pivot table but throw the
second column of the table into the "Column Labels" area and also put it into
the values box as a count. You then have a matrix with only single
occurences of the ID. with a one in the columns for where there is a value
recorded.

You can then replace (by column) the 1 with teh vlue of that column and then
put a concatenate statement at the end of each row.

If you need an example I can e-mail you something.

 

[Guest]

thats exactly wt i did later ....thnx 4 ur help paul......