Highlight only the first duplicate number that found in a column

  • Kindly please help me, I have sheet 1 and in [TABLE="class: grid, width: 500, align: center"]

    [tr]


    [td]

    c3

    [/td]


    [td]

    d3

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1st

    [/td]


    [td]

    copy

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1347

    [/td]


    [td]

    0934

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1258

    [/td]


    [td]

    1258

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    8753

    [/td]


    [td]

    2367

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    9032

    [/td]


    [td]

    3789

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1258

    [/td]


    [td]

    5934

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4356

    [/td]


    [td]

    6367

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1258

    [/td]


    [td]

    7589

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    8901

    [/td]


    [td]

    9378

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    this two column compare for duplicates and I manage to highlight (actually not bold) the number that is duplicate but I dont want to highlight the 2nd, 3rd & so on duplicate number in c3 (highlight 1 only from the 3 numbers most of the times got 2 only) . (row start from c3 to c2000 and d3 is up to d2000 also). I already use CF for highlighting the duplicate in C3 which my formula in CF is


    =IF(ISERROR(MATCH(c3:c3,$c$3:$c$780,0)),"",c3:c3) I just try to do this formula and it works, but I dont know how to command not to highlight the 2nd, 3rd & so on duplicate number in C3 (C3 only can have duplicate number within the column; D3 doesn't have any repeat number in the D column.


    I need to do the same formula command for other 2 partner columns (compare for duplicates this two columns & the 1st column if have to many duplicates highlight the first number that have more than 1 duplicates only) cells need to do again is for E3 & F3; G3&H3; until AU3&AV3..


    Kindly please try to help me, I cant finish my work and my brain want to burst already (didn't study this but trying to learn from here...


    Hope to hear from you all soon.. Thx


    P.s.


    is it possible to count how many highlighted cell (not bold) there is in a column (I mean total highlighted cell) and put the answer in cell C1, E1, G1, I1 ... AS1 , AU1. Tq again..

  • Re: Highlight only the first duplicate number that found in a column


    Just to explain the reason for cross post links - this is so people here don't spent time working on stuff that has already been solved on another forum :)


    this formula copied down appropriate range will return True for the the first occurrence of a duplicate value in Column C. Adapt for other columns as necessary.


    =COUNTIF($C$3:C3,C3)=2

  • Re: Highlight only the first duplicate number that found in a column


    Quote

    is it possible to count how many highlighted cell (not bold) there is in a column (I mean total highlighted cell) and put the answer in cell C1, E1, G1, I1 ... AS1 , AU1. Tq again..


    Sorry I don't know how to do this part.

  • Re: Highlight only the first duplicate number that found in a column


    Just to clarify the formula in Post #5 goes in conditional formatting, not actually in a range. Then you set the conditional format to format the cells however you want.


    The second part of your question would then need to count conditional formats in a certain colour. That is the part I don't know how to do without the help of helper cells.

  • Re: Highlight only the first duplicate number that found in a column


    The formula in my Post #5 is wrong. It finds the first duplicate value, ie the second time a number is listed in a column. So Rowddawg question has not been answered yet :)

  • Re: Highlight only the first duplicate number that found in a column


    Hi again


    Select C3:C2000 and then apply conditional formatting using this formula and set the required colour.


    =AND(COUNTIF($C$3:C3,C3)=1,COUNTIF($C$3:$C$2000,C3)>1)


    This will highlight the first occurrence only of any duplicates.


    I'm still trying to understand what needs to happen for columns E, F, G, H, etc.

  • Re: Highlight only the first duplicate number that found in a column


    Thank you got it..


    Have another question (look like the table I send), if for column E & column F (E column - the number there need to check for duplicates (sometimes have same number within this column); MUST CHECK in F column (F is no repeat number).


    e.g. E3 only (cannot include E4 to check, need to check 1 row at a time for the F3:F2000 column) must check if have duplicate in F3:F2000 then highlight)


    E F
    DATE NEW PARTNO MASTER LIST
    1 04.08.2013 8425 0013
    2 06.08.2013 9992 0026
    3 06.08.2013 4505 0039
    4 07.08.2013 8425 1435
    5 10.08.2013 5325 2839
    6 13.08.2013 0013 2938
    7 29.08.2013 0013 3879


    Need to highlight only the number that has duplicate in F, if the number like in E1 and E4 it cannot be highlight coz theres no duplicate in the F column. The E6 & E7 can be highlight coz there's a duplicate number in F column, even if it is more than two times appear it's ok to be highlighted.


    Thank you very much for the help... I really learn so much in this forum..

  • Re: Highlight only the first duplicate number that found in a column


    Can you upload workbook please? Edit out any confidential information.


    To upload press "Go Advanced" and then press the "Paperclip".

  • Re: Highlight only the first duplicate number that found in a column


    Thank you its already settled got some points from you and the others... Thank you very much... I treat you for a cup of coffee... :)

  • Re: Highlight only the first duplicate number that found in a column


    Doesn't look like it uploaded so I am just going to take a [hopefully] lucky shot and see what happens.


    Use this as CF formula for E3:E2000


    =AND(COUNTIF($E$3:$E$2000,E3)>1,IFERROR(MATCH(E3,$F$3:$F$2000,0),0)>0)


    And this as CF formula for F3:2000


    =COUNTIF($E$3:$E$2000,F3)>1


    Edit: Coffee white no sugar, thanks

  • Re: Highlight only the first duplicate number that found in a column


    White Coffee? Sure M'sia got nice Old Town White Coffee!!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!