Consolidate Rows, Concatenate Data

[atx7953]

Hello,


I need to consolidate rows that have duplicate records, emails addresses, and concatente a unique data piece from the record into one field. I found a post using concatenate, but could not quite get the output I was looking for.


Example


Name Email Model#

Bob bob@bob.com A

Bob bob@bob.com B

Bob bob@bob.com C


Would turn into:

Name Email Model #

Bob bob@bob.com A,B,C


Any direction is appreciated!

 

[SirJB7]

Hi, atx7953!

If you had your data form cell A1 to C1 and down, you can write:

Row 1 (D, E, F): Order, Selected, Chained values

D2: =SI(Y($A2=$A1;$B2=$B1);D1+1;1) -----> in english: =IF(AND($A2=$A1,$B2=$B1),D1+1,1)

E2: =SI(D3=D2+1;"";"X") -----> in english: =IF(D3=D2+1,"","X")

F2: =SI(Y($A2=$A1;$B2=$B1);CONCATENAR(F1;",";C2);C2) -----> in english: =IF(AND($A2=$A1,$B2=$B1),CONCATENATE(F1,",",C2),C2)

Copy down D2:F2.

Sort worksheet by A, B, C.

When you finish, you'll have the chained values in rows marked with "X" in E column.

Maybe you have to copy and paste special (values) in order to sort properly to get all the "X" in adjacent rows.

Regards!

 

[selobu]

You can doit much easy by using Salstat2 https://code.google.com/p/salstat-statistics-package-2/wiki/PivotData


so import your data from the excel file,

and copy the following code into the script panel:


res= group()# calling the class to group the data

res.xdata= [ grid.GetCol('A'), grid.GetCol('B')] # getting the columns

res.ydata= [ grid.GetCol('C')]

res.yvalues= ['concat(C)'] # calling the function to concatenate de column

report.addPage() # adding a new page

for row in res.getAsRow():

__ report.addRowData( row) # report the results row by row


# note: don forget to remove the underscore of the code

 

[SirJB7]

Hi, selobu!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your comment...


Keep in mind that you're providing a new solution in a topic of more than 1 year ago, so your post will be mostly read for users searching for the same issue but surely not for the OP.


Regards!