How to compare columns with different format in excel 2016

kdschool
kdschool used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.

Author

Commented:
Where do I put in a separate column?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Yes. In a separate blank column. Say if column C is blank, place this formula in C2 and then copy it down.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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.

Author

Commented:
It does work just does not solve my problem.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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?

Author

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?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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?

Author

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.

Author

Commented:
Excellent advise. Very smart, informative and patient with someone with very little excel skills. Thank you

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