How to compare columns with different format in excel 2016

Using excel 2016 and trying to compare one column to see what is missing in the second column.  In column 1 the number has quotes in column 2 is does not examples Column 1 "296510002296510003"  Same number in Column 229651000;2296510003 has semi-colon.  I need to make sure every number in column 1 is in column 2.
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.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming number surrounded with double quotes is in A2 and number containing a semi-colon is in B2, then try this...
=SUBSTITUTE(A2,"""","")=SUBSTITUTE(B2,";","")

Open in new window

The above formula will return True if the numbers in A2 and B2 are same ignoring the double quotes and semi-colon.
kdschoolAuthor Commented:
Where do I put in a separate column?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yes. In a separate blank column. Say if column C is blank, place this formula in C2 and then copy it down.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

kdschoolAuthor Commented:
I got it but that does not work.  IT looks like it's doing a one to one comparison  The columns are not in order the sort does not match so I need for it to look at the entire column and find the number if it's in the other column.
kdschoolAuthor Commented:
It does work just does not solve my problem.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you want to check whether a number in double quotes in column A is found in column B which has numbers with a semi-colon, place this formula in C2
=ISNUMBER(MATCH(SUBSTITUTE(A2,"""",""),INDEX(SUBSTITUTE($B$2:$B$100,";",""),),0))

Open in new window

This formula will return TRUE if number with double quotes in A2 is found in the range B2:B100.
Copy the formula down and it will return True or False for other numbers in column A.

Also, adjust the range B2:B100 in the formula as per your requirement.
kdschoolAuthor Commented:
Please take a look at my spreadsheet.  The formula works but I checked and some on the list are in both columns even though false.
Book4.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Place this formula in C2 and copy it down.
=ISNUMBER(MATCH(SUBSTITUTE(A2,"""",""),INDEX(SUBSTITUTE($B$2:$B$733,";",""),),0))

Open in new window

Then give me one example where you think a number is found in both the columns but the formula is returning False. Give me the cell addresses of that number in both the column.
kdschoolAuthor Commented:
Here is example
New Entered Numbers      Original Numbers      
"554500000554500001   554500002554500003"       206020000           FALSE
"206020000"      208550000;208550000      TRUE

206020000       in both columns says false
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Was it difficult for you to enter the formula I posted in my last reply in your sample file and let me know the cell address of one case where it returns False and you expect True? Let me know the cell address of matched number from column A and B as well.
kdschoolAuthor Commented:
I did not understand.  Here is the spreadsheet again with the two numbers in both columns that are highlighted in yellow and your forumla is in column C
Book4-2.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In your file, C3 returns True because the number in A3 (206020000) is found on B2. Is this incorrect?

Let me explain what that formula is doing. The formula will take a number from column A and see if it is also found in the range B2:B733 and if number is found it returns True in corresponding cell in column C.

If that's not you are trying to achieve, will you take a minute to rephrase your requirement?
kdschoolAuthor Commented:
I see what you are saying  I think I got confused because I see the number in both columns one says false one true so the

"206020000"      208550000;208550000      TRUE

So this means that 206020000  is in both columns

And this one
"554500000554500001554500002554500003"      206020000      FALSE
means 554500000554500001554500002554500003 number is not in both columns?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's correct. If you find a True in column C against any number in column A, that means the number is found in both the columns A and B.
And for any number in column A, if the formula returns False in corresponding row in column C, that means the number is present in column A but not in column B.

Does this resolve your issue now?

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:
I can enter a new question if you need me to.  Is there a way to just select the two columns and highlight the ones that are in both columns even though the special characters are there.  It would be so much easier for me to just see the ones that are not duplicates that have to be fixed.
kdschoolAuthor Commented:
Excellent advise. Very smart, informative and patient with someone with very little excel skills. Thank you
Rob HensonFinance AnalystCommented:
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.