locked
Matching data in three columns RRS feed

  • Question

  • I have the following formula that matches data in three columns:

    =IF(C2="","",IF(SUMPRODUCT(($A$2:$A$28=A2)*($B$2:$B$28=B2)*($C$2:$C$28=C2))>1=TRUE,C2+1,C2))

    What I would like is a macro that would replace one the duplicate numbers in column C so the duplication is removed (for example if A2 and B2 are the same as A5 and B5 and C2 and C5 both contain 15, change c2 to 16 and leave C5 at 15).

    There are some instances where coulmn A and B match and column C is empty. This should remain empty.

    Monday, July 30, 2012 4:19 PM

Answers

  • Change your formula in row 2 to:

    =IF(C2="","",C2 + SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))-1)

    and copy down, then copy and paste values over column C.

    In a macro, that would be (with column D empty):

    Sub Macro1()
        With Range("D2:D" & Cells(Rows.Count, "C").End(xlUp).Row)
            .FormulaR1C1 = _
            "=IF(RC[-1]="""","""",RC[-1] + SUMPRODUCT((R2C1:RC1=RC[-3])" & _
                           "*(R2C2:RC2=RC[-2])*(R2C3:RC3=RC[-1]))-1)"
            .Copy
            Range("C2").PasteSpecial Paste:=xlPasteValues
            .Clear
        End With
    End Sub


    HTH, Bernie

    • Marked as answer by Xanthman Monday, July 30, 2012 5:19 PM
    Monday, July 30, 2012 4:52 PM

All replies

  • Change your formula in row 2 to:

    =IF(C2="","",C2 + SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))-1)

    and copy down, then copy and paste values over column C.

    In a macro, that would be (with column D empty):

    Sub Macro1()
        With Range("D2:D" & Cells(Rows.Count, "C").End(xlUp).Row)
            .FormulaR1C1 = _
            "=IF(RC[-1]="""","""",RC[-1] + SUMPRODUCT((R2C1:RC1=RC[-3])" & _
                           "*(R2C2:RC2=RC[-2])*(R2C3:RC3=RC[-1]))-1)"
            .Copy
            Range("C2").PasteSpecial Paste:=xlPasteValues
            .Clear
        End With
    End Sub


    HTH, Bernie

    • Marked as answer by Xanthman Monday, July 30, 2012 5:19 PM
    Monday, July 30, 2012 4:52 PM
  • Great!

    Thank you.

    Monday, July 30, 2012 5:20 PM
  • =IF(C2="","",

    I prefer using number 0 insted of "" because 0 is number type as the original data then use format as 0;-0; to hide 0. Using "" is text type that will be sorted diferently from using 0.

    Use =IF(C2=0,0, insted or omit it all and let your formula calculates as itself without using If.

    Tuesday, July 31, 2012 1:41 AM
  • Hi,

    The information of Somkiat is also helpful. ^^


    Jaynet Zhang

    TechNet Community Support

    Tuesday, July 31, 2012 2:13 AM