Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
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. -- Paul Mugleston Data Officer - UK "Malik" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
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.. "Paul Mugleston" wrote: 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. -- Paul Mugleston Data Officer - UK "Malik" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
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 -- Paul Mugleston Data Officer - UK "Malik" wrote: 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.. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
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. "Paul Mugleston" wrote: 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 -- Paul Mugleston Data Officer - UK "Malik" wrote: 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.. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
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. -- Paul Mugleston Data Officer - UK |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
thats exactly wt i did later ;)....thnx 4 ur help paul......
"Paul Mugleston" wrote: 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. -- Paul Mugleston Data Officer - UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |