Excel formula to combine two lists without duplicates

Author: Oscar Cronquist Article last updated on December 29, 2018

Question: I have two ranges or lists [List1 and List2] from where I would like to extract a unique distinct list? Merge two lists without duplicates, in other words.

Answer:

Update! 2017-09-01, smaller regular formula in cell F3:

=IFERROR[LOOKUP[2, 1/[COUNTIF[$F$2:F2,$B$3:$B$21]=0], $B$3:$B$21], LOOKUP[2, 1/[COUNTIF[$F$2:F2, $D$3:$D$8]=0], $D$3:$D$8]]

Explaining regular formula in cell F3

This formula consists of two similar parts, one returns values from List1 and the other returns values from List2.

Step 1 - Prevent duplicate values

The COUNTIF function counts values based on a condition, in this case, I am counting values in cells above. This makes sure that duplicates are ignored.

COUNTIF[$F$2:F2,$B$3:$B$21]=0

becomes

COUNTIF["Unique distinct list",{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "}]=0

becomes

COUNTIF["Unique distinct list",{"Federer, Roger "; "Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay "; "Roddick, Andy ";"Del Potro, Juan Martin "; "Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando "; "Blake, James ";"Nalbandian, David ";"Robredo, Tommy "; "Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "}]=0

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}=0

and returns

{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE}

Step 2 - Divide 1 with array

The LOOKUP function ignores error and if we divide 1 with 0 an error occurs. 1/0 = #DIV/0!

1/[COUNTIF[$F$2:F2,$B$3:$B$21]=0]

becomes

1/{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE}

and returns

{1;1;1;1; 1;1;1;1;1; 1;1;1;1;1;1; 1;1;1;1}

Step 3 - Return value based on array

LOOKUP[2, 1/[COUNTIF[$F$2:F2,$B$3:$B$21]=0], $B$3:$B$21]

becomes

LOOKUP[2, {1;1;1;1; 1;1;1;1;1; 1;1;1;1;1;1; 1;1;1;1}, $B$3:$B$21]

becomes

LOOKUP[2, {1;1;1;1; 1;1;1;1;1; 1;1;1;1;1;1; 1;1;1;1}, {"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "}]

and returns Almagro, Nicolas in cell F3.

Step 4 - Return values from List2

When values run out from List1 formula1 returns errors, the IFERROR function then moves to formula2.

IFERROR[formula1, formula2]

formula2 is just like formula1 except that it returns values from List2.

Array formula in F3:

=IFERROR[IFERROR[INDEX[$B$3:$B$21, MATCH[0, COUNTIF[$C$1:C1, $B$3:$B$21], 0]], INDEX[$D$3:$D$8, MATCH[0, COUNTIF[$C$1:C1, $D$3:$D$8], 0]]], ""]

How to create an array formula

1. Select cell C2
2. Press with left mouse button on in formula bar
   
3. Paste array formula to formula bar
4. Press and hold Ctrl+ Shift
5. Press Enter
6. Release all keys

Recommended article

Recommended articles

How to copy formula

1. Copy cell C2
2. Select cell range C3:C19
3. Paste

Earlier versions of Excel 2007

Array formula in cell C2:

=IF[ISERROR[INDEX[$A$2:$A$20, MATCH[0, COUNTIF[$C$1:C1, $A$2:$A$20], 0]]], INDEX[$B$2:$B$7, MATCH[0, COUNTIF[$C$1:C1, $B$2:$B$7], 0]], INDEX[$A$2:$A$20, MATCH[0, COUNTIF[$C$1:C1, $A$2:$A$20], 0]]]

Get excel *.xls file

how-to-extract-a-unique-distinct-list-from-two-columns-in-excel-2003.xls
[Excel 97-2003 Workbook *.xls]

Unique distinct values from two columns, no blanks

1. 04-28-2014, 07:22 AM #1

   hi, i'm looking to merge two lists of names into one combined list with only unique values. the two lists have mostly the same info but list 1 contains some info not in list 2 and visa versa. i need it so that each list can be updated and the combined list will automatically update itself. can this be done??

   Rob

2. 04-28-2014, 08:22 AM #2

   Hi rbncpr, Yes it can be done. Please attach sample worksheet. You can use "&" to combine two names.

   Regards,

3. 04-29-2014, 11:52 AM #3

   Merge lists.xlsx

   here's an example of what i'm trying to do!

4. 05-01-2014, 05:12 AM #4

   hi rbncpr, This can be done but you will use another column but if you don't prefer using another column.I think VBA code is needed for this. What do you prefer?

   Thanks

5. 05-01-2014, 07:27 AM #5

   i have no problem with using another column, i'd like to avoid VBA if possible and keep it with formulae!

6. 05-01-2014, 10:16 AM #6

   Hi rbncpr this formula will work on your sample worksheet,but if you will add data,i think you also need to change conditions in the formula. on C3, paste this formula =IF[COUNTIF[list1,A2]=1,A2,IF[COUNTIF[list2,B2]=1,B2,""]] on D3,paste this formula,=IF[COUNTIF[$C2:$C$15,C2]=1,C2,""]

   you will see your required result in D3

7. 05-01-2014, 10:18 AM #7

   Hi,

   Go to Name Manager and define Rowz as:

   =ROW[INDIRECT["1:"&ROWS[list1]*2]]

   Your array formula** is then:

   =IFERROR[INDIRECT[TEXT[SMALL[IF[FREQUENCY[IF[$A$2:$B$15"",MATCH[$A$2:$B$15,T[OFFSET[$A$2,INT[[Rowz-1]/2],MOD[Rowz-1,2],,]],0]],Rowz],[10^3*[ROW[$A$2]+INT[[Rowz-1]/2]]]+1+MOD[Rowz-1,2]],ROWS[$1:1]],"R000C000"],0],""]

   Copy down until you start to get blanks for the results. Regards

   **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula [though do not attempt to manually insert these yourself].

8. 05-01-2014, 10:21 AM #8

   Use this formula in C2 =IFERROR[INDEX[list1,MATCH[0,INDEX[COUNTIF[$C$1:C1,list1],,],]],IFERROR[INDEX[list2,MATCH[0,INDEX[COUNTIF[$C$1:C1,list2],,],]],""]]

   	A	B	C
   1	List 1	List 2	List 3
   2	AAB	AAC	AAB
   3	AAC	AAC	AAC
   4	AAD	AAD	AAD
   5	AAD	AAF	AAE
   6	AAD	CCA	BBA
   7	AAE	CCA	BBC
   8	BBA	BBA	zaza
   9	BBA	BBA	zzz
   10	BBA	BBD	AAF
   11	BBA	BBG	CCA
   12	BBC	poof	BBD
   13	BBA	Pran	BBG
   14	zaza		poof
   15	zzz		Pran

   If you like my answer please click on * Add Reputation
   Don't forget to mark threads as "Solved" if your problem has been resolved "Nothing is so firmly believed as what we least know."

   --Michel de Montaigne

9. 05-01-2014, 10:51 AM #9

   I should've mentioned that I was attempting to design a solution which could be extended [with suitable modifications] to an arbitrary number of columns, not just two. If you in fact will only ever have two, then it would make sense to take e.g. AlKey's solution, which is simpler and less resource-heavy.

   Regards

10. 05-06-2014, 04:30 AM #10

    thanks everyone for yere help... i finally have it working the way i wanted! AlKey's solution is working well, cheers!!

11. 05-06-2014, 04:36 AM #11

    Please close the thread if you got your solution.

    You can have a look at this also in the mean time

    Please make the Post as solved, when you get your answer & Click * if you like my suggestion

12. 05-06-2014, 08:09 AM #12

    You're welcome and thank you for the feedback!

    Please mark thread as "Solved" if your issue has been resolved. [Selecting Thread Tools-> Mark thread as Solved]

13. 01-12-2020, 06:56 PM #13

    hi all

    Does anyone know how to do the above with the new dynamic array formulas?

14. 01-12-2020, 06:58 PM #14

    Administrative Note: We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    Ali
    
    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.

    Forum Rules [updated September 2018]: please read them here.

    
    How to use the Power Query code you've been given: help here. More about the Power suite here.