Conditional Formatting not highlighting duplicates correctly

[itsjohnkim]

I am trying to remove items from my catalog that are discontinued. I added the column of new P/N so I can match it up with my current catalog to quickly identify those products. Those that are not highlighted should be the discontinued items. However, I see P/N that are highlighted when there is only one.

Any suggestions , please ?

 

[inactiveuserps07]

@itsjohnkim, if you can provide some sample data, including column letters and row numbers, I'm confident I or someone else here can help you find and address the problem. But we do need to see the data that you are seeing, in order to offer suggested solutions.

 

[itsjohnkim]

Example below: I have over 55k lines but 0.0000021 is highlighted as excel is saying there is a duplicate. However when I do the FIND option, there is no other duplicate. It is also highlighting close variations, but not exact match.


[TABLE="width: 288"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]MFR PART NO[/TD]
[TD]Essendant #[/TD]
[TD]OEM #[/TD]
[/TR]
[TR]
[TD]0.0000021[/TD]
[TD]AAG1041200[/TD]
[TD]1041200[/TD]
[/TR]
[TR]
[TD]0.04541[/TD]
[TD]AAG1041905[/TD]
[TD]1041905[/TD]
[/TR]
[TR]
[TD]0.05904[/TD]
[TD]AAG1048201[/TD]
[TD]1048201[/TD]
[/TR]
[TR]
[TD]0.07008[/TD]
[TD]AAG1048901[/TD]
[TD]1048901[/TD]
[/TR]
[TR]
[TD]00005[/TD]
[TD]AAG1050200[/TD]
[TD]1050200[/TD]
[/TR]
[TR]
[TD]00005-A[/TD]
[TD]AAG1050905[/TD]
[TD]1050905[/TD]
[/TR]
[TR]
[TD]00007050[/TD]
[TD]AAG105420020[/TD]
[TD]105420020[/TD]
[/TR]
[TR]
[TD]00011[/TD]
[TD]AAG105490520[/TD]
[TD]105490520[/TD]
[/TR]
[TR]
[TD]00011-A[/TD]
[TD]AAG1064200[/TD]
[TD]1064200[/TD]
[/TR]
[TR]
[TD]00018[/TD]
[TD]AAG1064905[/TD]
[TD]1064905[/TD]
[/TR]
[TR]
[TD]00018-A[/TD]
[TD]AAG122200[/TD]
[TD]122200[/TD]
[/TR]
[TR]
[TD]00019CT[/TD]
[TD]AAG122905[/TD]
[TD]122905[/TD]
[/TR]
[TR]
[TD]00022CT[/TD]
[TD]AAG141900[/TD]
[TD]141900[/TD]
[/TR]
[TR]
[TD]00078073007981[/TD]
[TD]AAG182200[/TD]
[TD]182200[/TD]
[/TR]
</tbody>[/TABLE]

 

[inactiveuserps07]

@itsjohnkim,

We really need column letters and rows included.

You also mentioned a "new P/N" column. That should be included in your sample data.

What is the formula you're using for MATCH() that isn't giving you what you want? Where is it?

Remember: we can't see what you can see. And we'll need to see what you see, in order to help you solve the issue.

 

[thangdut]

I know i'm digging up an old thread. but i found a resolution that worked for me and it might help someone who would stumbled upon this thread as i did.

My Excel 2010 somehow recognizes some number-like cells with more than 9 or 10 characters to be alike, hence the wrong duplicate highlighting. A work-around is to add one or some SAME characters to the whole column to make those cells not number-like anymore. One way is to create an additional column = old column value & "a", then do the highlight duplicates filter on that column instead.