• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlight Duplicates in Excel in same Column with a Different Color every other duplicate

ChaCha90

New Member
Is there a formula to highlight duplicates in Excel in the Same Column with a Different Color every other duplicate item/number? Example, I have a group of NIINs/numbers in a column, I want to have a different color every of other number i.e. 1st duplicate color red, next duplicate numbers yellow, next group of duplicate numbers red, then next yellow within the same column red then yellow every other duplicate number.
 
Attached is a sample data spreadsheet. I only used about 30 numbers but at times it could be in the hundreds. Column "g" is the custom format that I would like to have as an end result starting with the "original format col "b", ending in the custom format in column "g" without me having to do it manually. Alternating colors can be any color, pink & green or yellow & red. "Conditional Formatting" duplicates all the NIINs in RED that is what I do NOT want, column "d"
 

Attachments

  • Duplicate Numbers in Same Column with a Different Color every other duplicate number-28 Feb 2...xlsb
    9.9 KB · Views: 10
Hi ChaCha90, see if attached is suitable. I used 4 helper columns that are rather straightforward.
 

Attachments

  • Copy of Duplicate Numbers in Same Column with a Different Color every other duplicate number-...xlsb
    11.8 KB · Views: 19
Last edited:
Similar to GraH - Guido but consolidated a variation of the formulae into two formulae (no helper columns) and applied to column B.
The formulae used are showing in cells G2:H2 but they're just to show my working and can be deleted.

=AND(COUNTIF($B$2:$B$39,$B2)>1,ISODD(SUM(1/COUNTIF($B$2:$B2,$B$2:$B2))))
and
=AND(COUNTIF($B$2:$B$39,$B2)>1,ISEVEN(SUM(1/COUNTIF($B$2:$B2,$B$2:$B2))))
 

Attachments

  • Chandoo43743Duplicate Numbers in Same Column with a Different Color every other duplicate num...xlsx
    11 KB · Views: 14
Very neat, @p45cal, I only recently discovered how constructions like "COUNTIF($B$2:$B2,$B$2:$B2)" work and the concept still has to grow on me :) ... So thanks for the example.
 
@p45cal, I have one more question, a minor change concerning if the formula can show every other color like how I have it in column "e"? If not then I will use what you sent to me originally. Truly appreciate your assistance.
 

Attachments

  • Chandoo43743(minor change) Duplicate Numbers in Same Column with a Different Color every othe...xlsb
    14.4 KB · Views: 4
I can't think of a minor change to effect that but in the unlikely event I think of something I'll let you know…
 
In the attached, on the Minor change… sheet, conditional formatting applied:
Code:
=AND(COUNTIF($B$2:$B$39,$B2)>1,ISODD(ROUNDUP(SUM(IFERROR(1/(COUNTIF($B$2:$B$39,$B$2:$B$39)*(ROW($B$2:$B$39)<=ROW())*(COUNTIF($B$2:$B$39,$B$2:$B$39)>1)),0)),0)))

and:

=AND(COUNTIF($B$2:$B$39,$B2)>1,ISEVEN(ROUNDUP(SUM(IFERROR(1/(COUNTIF($B$2:$B$39,$B$2:$B$39)*(ROW($B$2:$B$39)<=ROW())*(COUNTIF($B$2:$B$39,$B$2:$B$39)>1)),0)),0)))

Not trivial, but I'm sure it could be shorter; I stopped as soon as I got it working - perhaps someone cleverer than me can finesse the offering.
 

Attachments

  • Chandoo43743(minor change) Duplicate Numbers in Same Column with a Different Color every othe...xlsx
    17.6 KB · Views: 15
Thank you @p45cal, the formula for "Highlight Duplicate Numbers Different Color in the Same Column" will help me tremendously to complete my work tasks.
 

Attachments

  • Chandoo43743 (formula) Highlight Duplicate Numbers Different Color in Column.xlsb
    10.6 KB · Views: 20
Back
Top