Highlight duplicates not grouped together

[Sandez]

Hi,

I'll appreciate any help here as I'm stumped and can't find a solution to this problem. I've perused the forums and other sites but couldn't find a solution. As the title implies, I want to try and highlight any cell that's a duplicate but not together. I currently have a spreadsheet with multiple columns and approximately 10,000 rows so far. I have column A which has a unique value in it, column B which can contain both unique and duplicate values, and column C which contains the date it was entered. I want to have all duplicate values in column B grouped together but still arranged in date order. What I want to achieve is when I add a new row with a unique value in A and I enter a duplicate value in B, that the cell in B be highlighted, but not if it's already apart of the group in column B (i.e. the cell above in column B is the same). This will then allow me to cut and insert the new row into the existing group.

The rows need to be grouped together by column B but sorted by column C (by the first date of each 'group'), which the sort feature won't work.

[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]X001[/TD]
[TD]E111[/TD]
[TD]1/1/16[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]X002[/TD]
[TD]E111[/TD]
[TD]1/1/16[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]X003[/TD]
[TD]E111[/TD]
[TD]1/1/16[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]X004[/TD]
[TD]E111[/TD]
[TD]1/1/16[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]X005[/TD]
[TD]E222[/TD]
[TD]1/1/16[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]X006[/TD]
[TD]E222[/TD]
[TD]1/1/16[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]X007[/TD]
[TD]E333[/TD]
[TD]2/1/16[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]X008[/TD]
[TD]E333[/TD]
[TD]2/1/16[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]X009[/TD]
[TD]E111[/TD]
[TD]2/1/16[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]X010[/TD]
[TD]E777[/TD]
[TD]3/1/16[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]X011[/TD]
[TD]E666[/TD]
[TD]3/1/16[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]X012[/TD]
[TD]E555[/TD]
[TD]3/1/16[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]X013[/TD]
[TD]E555[/TD]
[TD]3/1/16[/TD]
[/TR]
</tbody>[/TABLE]


In this example, what I would like is for cell B9 to be highlighted. Then when I cut and insert the row into row 5, the highlighted cell will no longer be highlighted as it is now grouped with the duplicate values in column B.

I've tried a number of conditional formatting options but I can't seem to overcome the issue of 'isolating' duplicates that are not grouped together. It will either highlight B2:B4 and B9 (all but the first duplicate [using =COUNTIF($B$1:B1,B1)>1]), or highlight B4 and B9 (the last of each group of duplicates [using =AND(COUNTIF($B$1:B1,B1)>1,IF($B2=$B1,0,1))]).

If I need to make a macro to make it work then I'll do that, but I'd rather it be conditional formatting so it's 'live' when I'm adding a new row.

Sorry if it's a bit confusing, if it is, I'll try and clarify it further.

Any help would be greatly appreciated.

Thanks,
David

 

[ZVI]

Hi David,

Select B1 and try this CF formula: =AND(COUNTIF($B$1:B1,B1)>1,INDEX($B:$B,MAX(1,ROW()-1),0)<>B1)
Apply it to the column B.

Regards,

 

[Sandez]

Incredible! I should have just asked first. I appreciate your time and effort Vladimir. Thank you.

Regards,
David

 

[ZVI]

I'm glad it helped, David!