How to combine rows?

[Dina]

How can I do this?

Name| Company | City
Jon Smith| IBM| Toronto
Jon Smith| TD| montreal

into this:

Name| Company | Company2 | City | City 2
Jon Smith| IBM | TD | Toronto | Montreal

Thank you!!

 

[marysully]

Insert columns. Select Company column, right click to Insert column. Select
City column, right click to insert column. You will then have your 5 columns.

 

[Dina]

Sorry, I should clarify:

My data contains over 3000 entries. There are duplicates by name. The
maximum number of duplicates is 5. I would like to find an automated way to
go through the data and for each duplicated name do what the example showed.

Do you know of a way to do that?

Thanks in advance

 

[marysully]

I'm sorry, I don't. Hopefully someone else with this type of expertise can
help. Best of luck with it.

 

[Roger Govier]

Hi Dina

Insert a new column at C and title it Company2
Title column E City2
Title column F Duplicate

Sort your data ascending by column A
In C2 enter
=IF($A3=$A2,B3,"")
in E2 enter
=IF($A3=$A2,D3,"")
in F2 enter
=IF($A2=$A1,"Dup","")
Copy the formulae down for the extent of your data

Copy in turn each of columns C, E and F >Paste Special>values

Now sort your whole block of data by Column F ascending.
all your Duplicate rows will be at the end of the list and can be deleted.