Excel: How to Find Difference Between Two Columns

Software Engineer
Software Engineer used Ask the Experts™
on
Hello:

Attached is a simple spreadsheet containing two columns.  The first is data that was to be imported, while the second column contains data that was imported.

I'm trying to find what values in Column A did not get imported (i.e. do not match those of Column B).

I have searched online and tried several formulas that supposedly give this information.  But, all three formulas proved wrong.

Will any of you please give me the right answer?  :)

Thank you!

Software Engineer



difference.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Your data in column B is padded with space characters at the right. I got rid of them using the TRIM function, and was then able to test for values in column B that weren't in column A with this formula:
=COUNTIF(A:A,B2)=0

Open in new window


If I wanted to test values in column A that weren't in column B, I would have used:
=COUNTIF(B:B,A2)=0

Open in new window


I used the above formulas in Conditional Formatting to highlight the unmatched cells, but could also have done so in an auxiliary column.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Your file with Conditional Formatting as described in previous Comment.

I didn't see any cells that were highlighted yellow, but if you'd like to satisfy yourself the Conditional Formatting is working, try adding a character at the end of one of the values.
difference.xlsx
Consultant
Commented:
As an additional comment, the Before Import has 39 duplicate records.  If you eliminate the 39 duplicates, then trim the After Import, I believe you will see the records are identical in number count and cell-to-cell match.  See attached.
difference-New.xlsx

Author

Commented:
Thank you, Tom and byundt, for your excellent support!

John

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