asked on

Excel Formulas

In excel would have two columns with same numbers but different special characters. Would like to have formula that compares both columns, disregards the special characters and highlights the numbers in each column when they are in both columns or vice versa. So I can easily see the ones that are not in both columns. Example of both columns here.

Column A has numbers with double quotes, Column be no quotes but separates the numbers with semi colon. The columns do not match one to one they could be in any row in the column as you see here this row does not match but the value of the first one is in the column B also.
"210900000210900001" 211140000;211140001

Rob Henson

Continuation of this question:

https://www.experts-exchange.com/questions/29134852/How-to-compare-columns-with-different-format-in-excel-2016.html

Bill Prew

I think that is going to be near impossible to do in a formula with the data the way it is.

I would suggest your first step would be to break the cells apart that contain multiple numbers and place each single number in a cell in each column. Then you can use one of the LOOKUP() or MATCH() formulas quite easily to identify the duplicates.

»bp

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kdschool

ASKER

Subodh, this is amazing and exactly what I need. You are certainly an Excel Expert to have knowledge at this level. I am new to excel and have a very tight deadline to do this new assignment. I am very grateful to you for your help and again your patience with my low skill level in Excel. Thank you!!!!

Subodh Tiwari (Neeraj)

You're welcome! Glad I could help.
Thanks for the feedback.