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.

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

```
=ISNUMBER(MATCH(SUBSTITUTE(A2,"""",""),INDEX(SUBSTITUTE($B$2:$B$100,";",""),),0))
```

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.

Book4.xlsx

```
=ISNUMBER(MATCH(SUBSTITUTE(A2,"""",""),INDEX(SUBSTITUTE($B$2:$B$733,";",""),),0))
```

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.
New Entered Numbers Original Numbers

"554500000554500001 554500002554500003" 206020000 FALSE

"206020000" 208550000;208550000 TRUE

206020000 in both columns says false

Book4-2.xlsx

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?

"206020000" 208550000;208550000 TRUE

So this means that 206020000 is in both columns

And this one

"5545000005545000015545000

means 55450000055450000155450000

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
Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.