Excel - 2 lists - want to find duplicates!

[funkylad20]

I have 2 lists of numbers in Excel (6 digits each number).
Some of these numbers appear in both lists.
Some numbers only appear in one list.
I would like to cross-reference these lists and create another list of the singular, unique records.
I have done this before but cannot remember how I did it!

[tamborine]

Hilight first column and klik 'sort'. The column will be sorted from lowest to highest number. Same with second column. Then search/delete same numbers.

[funkylad20]

Yeah,I know sort...but the problem is, I am trying to avoid trailing down two seperate lists of thousands of records. There IS a way around it that will do this for you, just need to remember it...

[tamborine]

when you find it tell me; as I could do with knowing.

[Bushwhacker]

Copy and Paste both of your lists (one under the other) into column A of a new worksheet.

I will assume that you now have 5000 rows in column A

In cell B1 enter the formula :

=COUNTIF($A$1:$A$5000,A1)

Now fill the formula all the way down column B to cell B5000.

If you now Autofilter on column B, the entries that return a 1 in the filter will be the unique entries.

Just copy and paste these to another sheet.

That should give you what you want.

Cheers,

BW

[Huderon]

It's a while since I did this sort of thing (meaning you may need to tinker with the formula a bit), but here goes anyway.

Copy and paste both lists into the same worksheet, putting them all in column A,. Sort in ascending order. In column B, row 2 enter the formula =IF(A2=A1,"DUPLICATE","") and fill down to the end of the list. Filter on column B, selecting those entries which don't have the word DUPLICATE, then copy and paste into a new sheet.

You could also use VLOOKUP, but I'm not going to go there !

[tell-me-more]

The two methods above from Bushwhacker and Huderon solve slightly different problems.

Once you have created one long list from the two lists, if there are duplicates in this long list, do you want to keep one of the duplicates, or remove all instances of the number?

If you want to keep one of each number, Huderon's method works, if you only want to keep numbers that weren't duplicated (i.e. if there's two 301905s you get rid of both of them) then Bushwhacker's method works.

Hope that helps.