Conditional Formatting not highlighting duplicates correctly

itsjohnkim

New Member
Joined
Jul 16, 2017
Messages
2
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 ?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
@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.
 
Upvote 0
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.


MFR PART NOEssendant #OEM #
0.0000021AAG10412001041200
0.04541AAG10419051041905
0.05904AAG10482011048201
0.07008AAG10489011048901
00005AAG10502001050200
00005-AAAG10509051050905
00007050AAG105420020105420020
00011AAG105490520105490520
00011-AAAG10642001064200
00018AAG10649051064905
00018-AAAG122200122200
00019CTAAG122905122905
00022CTAAG141900141900
00078073007981AAG182200182200

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
@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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top