Oct 19 2021 08:52 AM
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!!! :)
Oct 19 2021 09:39 AM
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.
Oct 19 2021 10:58 AM
Oct 19 2021 11:11 AM - edited Oct 19 2021 11:13 AM
@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.
Oct 19 2021 11:45 AM
@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.