Multiple Unique Lists from Master List in Excel

Question

I have a list of companies with multiple contacts for each company, but I would like to create separate lists with 1 unique contact per company. E.g.

Original list
Name     Company    Email
John       AAA           [email protected]
David       AAA        [email protected]
Jane       BBB        [email protected]
Julia       CCC        [email protected]
Craigh       CCC        [email protected]
John       CCC        [email protected]

In this case, to have 1 unique record from each company in a separate list, I want to end up with 3 separate lists:

List 1
Name         Company         Email
John         AAA         [email protected]
Jane         BBB         [email protected]
Julia         CCC         [email protected]

List 2
Name         Company         Email
David         AAA         [email protected]
Craigh         CCC         [email protected]

List 3
Name         Company         Email
John         CCC         [email protected]

As you can see in each list there is only 1 record for each company.

Any help on how to do this would be most appreciated.

I have tried advance filter to list unique records only by selecting the entire original list as the range and setting the company column as the criteria see the unique records which I could then select visible cells and cut to a different list, and then re-run the filter again to create the next set of unique records etc...but the advance filter doesn't give the results as expected.

-

-

Answer 1

This might be a silly idea but still worth trying... Add a helper column with Count formula:

=COUNTIF($B$1:B2,B2)

Apply Filter and Sort your data set by the fourth column (Sort Smallest to Largest):

This seems to match your Table 1, 2 & 3 output.

Alternatively, if you still want to create separate tables, you can use the helper column and array formulas (Ctrl+Shift+Enter) as per example below:

=IFERROR(INDEX($A$1:$C$7,SMALL(IF($D$1:$D$7=1,ROW($D$1:$D$7)),ROW()-1),COLUMN()-5),"")

Change $D$1:$D$7=1 to =2 and =3 to replicate the 2nd and the 3rd table.

Edit: with additional "Location" column

=COUNTIFS($B$1:B2,B2,$D$1:D2,D2)