Combining information from rows

[Danielle]

I have an excel spreadsheet with lots of duplicate contacts, but their
information is spread throughout the different rows. For example, I have 3
rows for John Doe, but his address is in the first row, his phone number is
in the second row, and his affiliation is in the third row.

Is there a way to merge the information from all three rows into one? I have
a few thousand records like this.

Thanks!

 

[vezerid]

Question: are all your records like:

John Doe 1 A street
Jane Doe 3 B str
John Doe 212-555-5555
Mike Doe 7 C str
John Doe affil1
Jane Doe 313-444-4444
Mike Doe 414-666-6666
Jane Doe affil2
Mike Doe affil3

In other words, (1) do you always have the key in first column? (2) is
phone always in a later row than address and affiliation in a later
row than phone?

 

[Danielle]

The records are like this:
First Last Phone Address Affiliation
John Doe 555-555-5555
John Doe 123 Road
John Doe Yes
Jane Doe 555-555-5555
Etc.

Yes, the phone is always in a later row than address, and affiliation is in
a later row than that.

 

[vezerid]

First you need to produce a list of First/Last without duplicates. You
can use the advanced filter for this.
1. Insert an empty column after column B, to separate the first two
columns.
2. Click in A2. Issue Data>Filter>Advanced filter...
3. Filter the list in another location. Check H1. Check also Unique
records only.

At the end of this step you have just the names without duplicates.
Complete the headers next to First and Last. Now use the following
*array* formula in J2 (array formula: commit with Shift+Ctrl+Enter):

=INDEX(C$2:C$7,MATCH(1,($A$2:$A$7=$H2)*($B$2:$B$7=$I2)*(C$2:C$7<>""),
0))

You can copy the formula to the right and down for as many rows as
necessary. In my example I assumed data go up to row 7, which they
don't. Change the 7 to the last row with data. Leave the $$ as they
are.

HTH
Kostis