Link to home
Start Free TrialLog in
Avatar of BeGentleWithMe-INeedHelp
BeGentleWithMe-INeedHelpFlag for United States of America

asked on

Comparing 2 long lists in Excel ? Or another app?

I have 2 long lists (about 10K lines each, let's say)

1 list might look like

a
b
d
e
f
g


and the other looks like:
a
c
d
e
f
h

(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:
b
c
g
h

(all items not in both)

or
b
g
(all items in list 1 only)

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BeGentleWithMe-INeedHelp

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 : )
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial