Have data with multiple records associated to same ID

[Dccp_WV]

My Data Situation:

A B
33007014220000 33007014220000 DLL
33007014220000 CND
33007014220000 CBL

Need to get it like this:
A B
33007014220000 CBL,CND,DLL


Is there a way to do this via formulas or code? I am doing it by hand and
will take days to go through 1683 Unique records.

Thanks for your help!
Dan

 

[Pete_UK]

How many subsets are you likely to have (maximum)? Are the values DLL,
CND etc in column C, or are they tagged on to the end of the ID in
column B?

Pete

 

[Don Guillett]

One way. Put in c2 and copy down>change to values>delete col B
=IF(LEN(A2)<2,"",RIGHT(B2,3) & " " &RIGHT(B3,3)& " "& RIGHT(B4,3))

 

[Ashish Mathur]

Hi,

Download and install the following add-in -
http://www.download.com/Morefunc/3000-2077_4-10423159.html

Suppose your data is in range B5:C7. In B11, you have 33007014220000. In
cell C11, enter the following array formula (Ctrl+Shift+Enter)

=SUBSTITUTE(TRIM(MCONCAT(IF(1*LEFT($C$5:$C$7,LEN(B11))=B11,RIGHT(C5:C7,3),""),"
"))," ",",")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Dccp_WV]

i would like the list of values CND,DLL to be in column C. And it could be
anywhere from 2 records per ID to 10 records i'm not sure.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Dccp_WV]

Ashish,

I tried this route and it didn't work. I don't have admin privelages on this
machine so i don't think the add-on installed correctly. I tried the previous
entry and it worked for each individual record but there's no way to do it
for multiple records.

The problem is there isn't a defined number of dup records for each
individual key. One unique key could have as many as ten seperate log types
or as few as one. How do you tell EXCEL to look and match the duplicate keys
and list each unique record attached to it in the format i have specified?

 

[Dccp_WV]

I counted the number of duplicates, it ranges from 2-11.

 

[Dccp_WV]

I then sorted the entire list by the number of duplicate reocrds and used an
if statement to sort out the duplicates and attached the Concentate fromula
to populate on the cells that met the criteria.

=IF(AND(A3=A2,A4=A3,A5=A4),CONCATENATE(B2&",",B3&",",B4&",",B5),"")