Forum Discussion
Comparing two large excel sheets
I have two large excel files. I have to to combine the two files to see who is missing from the first file to the second file. The files are mostly the same besides a handful of differences. I cannot seem to find a way to isolate out the two files to only show who is missing from the first one compared to the second.
I can get the system to Highlight the duplicates, which is a step in the right direction BUT I dont want to see the duplicates and the list is over 60k so its a nitemare to try and look at manually.
Please help!!! 🙂
4 Replies
Gluksker I'd look into Power Query (as long as you are not on a Mac). Ideal to connect to large data sets without loading everything into a workbook and merge these data sets mimicking VLOOKUP, with the possibility to not only find matching records, but also find the ones that don't. Would be helpful if you could share a representative sample of your data sets to demonstrate it.
Hey,
there are multiple ways to compare two files. But it would require some additional information two find a possible solution.If for example you have a unique identifier (e.g. an ID), you could use a =UNIQUE() function on the column and use =COUNTIF() to count the occurrences for each ID in a separate file or worksheet for both files and compare the results. Based on this comparison you at least would know which rows are missing.
- So they all do have ID numbers. It’s an Active roster sheet. So I get the first sheet and shows me all active, when I get the next sheet, shows me all active again but will now have people missing that have been terminated. So I need to compare the two to find who has disappeared from the original sheet compared to the second sheet
Gluksker Hey again,
in that case you can use a lookup function, e.g.:
ID - Sheet 1
Check ID - Sheet 2 1 =XLOOKUP(A2;C:C;C:C;"Missing") 2 2 2 3 3 3 5 4 4 4 5 5 6 6 6 7 7 7 8 Missing 9 Missing 10 Missing But depending on your machine, this can take some time, or could lead the file to crash.
