Conditional Formatting - 2 columns of "general" numbers

See attached. I have 2 columns with SIM card numbers in them.

Due to the length of the numbers, I have inserted an apostrophe (') at the beginning of each number in my template (Column A is copied/pasted directly from my template into this spreadsheet), otherwise it changes the number to 8.898817E+18. As well, if I convert this column to a "number", (for example, A2 is 8988169214000455545), then it changes 8.898817E+18, which does change the correct number to something that does not match the original.

Note that Column D has been brought into the spreadsheet using the text wizard: Data>From Text>Space Delimited. When done this way, the numbers come into my spreadsheet WITHOUT an apostrophe (') at the beginning of the SIM card number.

I am using Conditional Formatting > Highlight Cell Rules > Duplicate Values. There ARE differences in each column, but the Conditional Formatting is not highlighting correctly. Instead ALL cells in both columns are highlighting.

What am I overlooking? Is it the fact that the numbers are not formatted the same? How can I get my template numbers (Column A) not to convert to 8.898817E+18 when inputted without the apostrophe (') at the beginning of the SIM card number?

I appreciate any and all help to resolve this issue.

Thank you,
-prefcomm

Originally Posted by 6StringJazzer

For example, look at row 183. The value in column A (8988169224001445858) does not appear in column D, but it appears 22 times in column A. That is considered a duplicate.

Are you trying to find cases only where data in one column appears in the other column?

I have done CTRL-F and looked for 8988169224001445858 and can only locate it on the spreadsheet ONCE on the entire page. I am not seeing where it is in column A 22 times. And if it is only one the sheet once, then it should NOT be highlighted because there is no duplicate of it anywhere else on the sheet. Correct?

Yes, I am trying to find cases where data in one column also appears in the other column. But I also want to see if there is data in one column (either one) that is not in the other column.

It would seem that since one column has 754 lines and the other has 745 lines that there should be some that are not in both columns. For a fact, each column itself should have no duplicates. Basically this is a checkoff to ensure we are billed for all active SIM cards. Column A is what we have on record as active, while column D is what our provider billed us for. There are typically discrepancies because column A is up-to-date while column D is what was active last month.

Make sense?

Originally Posted by prefcomm

I have done CTRL-F and looked for 8988169224001445858 and can only locate it on the spreadsheet ONCE on the entire page.

OK, maybe this is more insidious. I did a COUNTIF and got 22 on that one, but I did not look for where the other occurrences are. I'll come back after further examination.

6StringJazzer, did you have any luck figuring this one out?

Originally Posted by 6StringJazzer

Just finding behavior that I can't figure out. COUNTIF is matching things that don't actually match. I have never seen this before and still trying to narrow it down.

Thanks 6StringJazzer!! I value your input and look forward to your feedback soon!

I have isolated the problem and have posted another thread to see if we can get a solution to that specific problem.

https://www.excelforum.com/excel-for...ml#post4901347

Originally Posted by FDibbins

I added a helper column and just kept the right-most 15 characters, and the countif worked, not sure if this is something the OP would consider....

	G	H	I
2	169214000798449
3	169214000798381		1
4	169214000798384		1
5	169214000798344		1
6	169214000798444		1
7	169214000794444		1
8	169214000744444		1
9	169214000744444		2

G2=TRIM(RIGHT(A2,15))
I3=COUNTIF(G$2:G3,G3)
both copied down

The 1st 4 digits seems constant

FDibbons, this does resolve the issue in a roundabout way, however, this requires much more manual work to then go back to the original lists and locate those that did not highlight due to the conditional formatting. But this does do the trick.

6StringJazzer, I look forward to any answers you get to the new thread started.