Comparing 2 long lists in Excel ? Or another app?

BeGentleWithMe-INeedHelp used Ask the Experts™
I have 2 long lists (about 10K lines each, let's say)

1 list might look like


and the other looks like:

(some items are in both, some are in 1, not in the other and vice versa).

Is there a way to compare those in excel or elsewhere and have it get rid of the dupes?
so you get a list like:

(all items not in both)

(all items in list 1 only)

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can do it in Access in few minutes.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
It costs about $30 but you might want to consider Compare It.
Test sample in Access. Data in TAble1 and Table2. Open Query2. If count = 1, data is in only one of tables, 2 - in both. 1 - table1, 2 - table2.
But Access is more expensive if you don't have it :)
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I am using 2010 version of excel... I believe functionality exists in other versions too, it may be in different place.

In 2010...

1) put both lists into a single column.
2) select Data Tab
3) highilight the column
4) select Remove Duplicates Button
5) uncheck box if column does not have a header
6) click ok

See attached screenshot for correct button to use.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
The method suggested by buttersk will produce a list of items unique to List1, those unique to List2 and also those on both lists. I don't believe this is what BeGentleWithMe-INeedHelp is requesting.

Assuming that List1 is in column A and List2 in column D, put the following formulas in cells B1 and E1:
=IF(ISNA(MATCH(A1,D:D,0)),"Unique","")              formula for B1
=IF(ISNA(MATCH(D1,A:A,0)),"Unique","")              formula for E1

Copy these formulas down. The easy way to do so for long lists is to double-click the little square at the bottom right of the selection marquee. The formula will then be copied to the end of cells in the adjacent column (i.e. the list).

You can then use Data...Sort put the unique values in each list at the top. Select both the list and the column with the formula when you do the sorting.

Open in new window

So what I am doing is trying to compare 2 lists of files - data I was able to recover from a failed hard drive and the data a recovery service says they can get back.  I did a DIR /s > file and then brought that into excel.  690,000 lines (spaces, directory name, etc plus lots of files).  I tried to sort and it says it can't do that with the resources I have (8GB, new Optiplex PC running win 7 64 bit (although office is 32 bit I think).

Getting the list of files from the recovery service.  I'm looking to see what files are on their list that are not on my dir.  Those are the ones I am missing and I can decide if it's worth their charge.

so the number of records I am dealing with is much larger than I thought before.
yeah, butter - I did your method and got lots of files that are on both lists, along with those that are unique to either list.

by - I tried yours but after recalc took 5 minutes to do 1%, I hit escape : )

compare it - seems to work - you run a report and it shows differences?  I want to also see whuch are on both list.
Your choice should be Access if you have so many records.
I have access, so I'll give it a try - new database, 2 tables, each with the single column of file name and then a query to compare the 2?  that last step is probaby where I'll be back here.

als - thanks for that database.  I'll look at that for guidance.

oh.  I created 2 tables.  now how to fill each with the xlsx or csv ?  (access 2010)
OK, I am importing the excel sheets into diff. tables.

blogging in real time : )
May be you can leave only few records in each table, compact and repair it:
and upload. I can help with queries.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial