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
kdschoolAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached with two conditional formattings applied to the range A2:A2000 and B2:B2000. As a result of this, the cell will be highlighted with red color, white and bold font and a border around if the number in that cell is found in other column.
This will give you a visual clue if a number in any column is also available in other column.

But since the matching numbers are not found in the same row, visual clue only will not be of much help as you may want to confirm that where this number is exactly found in another column. To handle this scenario, I have also placed two formulas in column C and D (self explanatory) which will give you a complete detail of that particular number.

See if that helps.
Compare-Columns.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kdschoolAuthor Commented:
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)Excel & VBA ExpertCommented:
You're welcome! Glad I could help.
Thanks for the feedback.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.