MS Excel Compare and Match Data with two Unique Datasets

Question

In MS Excel spreadsheet, I have two columns containing contact information (Contact Names: A1:A8 and Contact Phone Numbers: B1:B8);

I would like to know if it's possible using a MS Excel formula that will compare and match contact name containing 1 or more datasets of telephone numbers and place in adjacent cell.

Although Column A contains duplicate names they each have unique telephone numbers that I would like to maintain only one list of Unique Contact Names and match each corresponding unique telephone number(s) with the contact name.

EXAMPLE

CONTACT NAME (A1:A8) CONTACT PHONE (B1:B8)

John Doe (212) 555-6666

Sally Jenkins (718) 555-5555

David Dodson (647) 555-5566

Henry Doe (718) 666-5555

John Doe (647) 666-6666

Jane Doe (212) 222-2222

Henry Doe (718) 566-6666

OUTPUT RESULTS

CONTACT NAME CONTACT PHONE 1 CONTACT PHONE 2

John Doe (212) 555-6666 (647) 666-6666

Sally Jenkins (718) 555-5555

David Dodson (647) 555-5566

Henry Doe (718) 666-5555 (718) 566-6666

Jane Doe (212) 222-2222

Answer 1

Here is a suggestion that does not involve formulas and has a slightly different layout as the end result. I'm not a big fan of combining several values into one cell, hence this alterntative: Use a pivot table.

First, you may want to turn your source data into an Excel Table. That has many benefits for working with data sets.

Then click any cell in the Excel Table and then Insert > Pivot Table.

In the Pivot Table Fields pane, drag the Name to the rows and the phone number to the rows, underneath the name.

The pivot table will now show the data grouped by name, with all phone numbers for a name nicely tucked in below the respective name.

With an Excel Table as the source for the pivot table, you can add more rows with names and numbers to the Excel Table, then refresh the Pivot and -- voila! -- all new entries will be there in the pivot table.

Let me know if that works for you.