I have two excel files with the same format. They both have 1 column with data. One has 800 records and the other has 805 records, but I am not sure which of the 5 in the 805 set are not in the 800 set. Can I find this out using excel?
+1
A:
It might seem like a hack, but I personally prefer copying the cells as text (or exporting as a CSV) into Winmerge or any other diff tool. Assuming the two sheets contain mostly identical data, Winmerge will show the differences immediately.
Juliet
2009-09-30 19:31:35
Wouldn't this be more work than writing a simple formula where the data is already contained?
NickSentowski
2009-10-02 18:20:23
Nick: There's always more than one way to crack a nut, but there's no right or wrong way to do it so long as you use the tools you're comfortable with.
Juliet
2009-10-02 18:47:48
I didn't say you were wrong... It just seems a bit complicated to me when Excel has the tools already built in. Once I get a result in the external tool, I'm probably going to need to bring it back to Excel to work with anyway.
NickSentowski
2009-10-02 21:33:22
+1
A:
Use the vlookup function.
Put both sets of data in the same excel file, on different sheets. Then, in the column next to the 805 row set (which I assume is on sheet2), enter
=if(isna(vlookup(a1, Sheet1!$A$1:$A%800, 1, false)), 0, 1)
The column will contain 0 for values that are not found in the other sheet, and 1 for values that are. You can sort the sheet to find all the missing values.
Abtin Forouzandeh
2009-09-30 19:35:53
+6
A:
vlookup is your friend!
Position your column, one value per row, in column A of each spreadsheet. in column B of the larger sheet, type
=VLOOKUP(A1,'[Book2.xlsb]SheetName'!$A:$A,1,FALSE)
Then copy the formula down as far as your column of data runs.
Where the result of the formula is FALSE, that data is not in the other worksheet.
NickSentowski
2009-09-30 19:36:28
A:
Use conditional formatting to highlight the differences in excel.
eric.a.booth
2009-09-30 19:40:14
The problem here is that every single cell after the first occurrence of a difference will then be highlighted. True, you could fix the first occurrence to find the next one. But you'll have to redrag the formula after each fix, and if you have a lot of differences, this is simply infeasible.
Greg
2009-09-30 19:46:51
+2
A:
COUNTIF works well for quick difference-checking. And it's easier to remember and simpler to work with than VLOOKUP.
=COUNTIF([Book1]Sheet1!$A:$A, A1)
will give you a column showing 1 if there's match and zero if there's no match (with the bonus of showing >1 for duplicates within the list itself).
hawbsl
2009-09-30 20:47:41
This process will work, and will be less work for the user. In terms of computing power, a VLOOKUP is more efficient unless you are checking for duplicate values as well.
NickSentowski
2009-10-02 18:22:55
A:
excel overlay will put both spreadsheets on top of each other (overlay them) and highlight the differences.
http://download.cnet.com/Excel-Overlay/3000-2077%5F4-10963782.html?tag=mncol
A:
Note that if using a VLOOKUP solution you are assuming the values in your data are exclusive. For example, if the extra 5 rows' data repeats already existing values, then they won't be identified.
Iznik
2009-10-06 07:36:50