Check for Duplicates then Sum cells of duplicates

Discussion in 'Microsoft Excel Programming' started by aileen, Dec 10, 2008.

  1. aileen

    aileen Guest

    I am checking for duplicates in a workbook. The following is the code I'm
    using:

    lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 1 Step -1
    With ActiveSheet
    If Cells(i, 11) = Cells(i + 1, 11) _
    And Cells(i, 6) = Cells(i + 1, 6) _
    And Cells(i, 4) = Cells(i + 1, 4) _
    And Cells(i, 5) = Cells(i + 1, 5) Then
    Cells(i, 12) = Cells(i, 3) + Cells(i + 1, 3)
    End If
    End With
    Next

    This works if there is only one duplicate entry, but I can have multiple
    duplicate entries. I need to sum all of the column C cells for each
    duplicate found. Is this possible?
     
    aileen, Dec 10, 2008
    #1
    1. Advertisements

  2. aileen

    Mike H Guest

    Hi,

    I can see what your code is doing but am not clear about the result you
    expect.

    take this data

    Columns
    C D E F K L
    7 1 2 3 4 ?
    9 1 2 3 4

    Mike


    What do you expect to see in column L

    Mike


    "aileen" wrote:

    > I am checking for duplicates in a workbook. The following is the code I'm
    > using:
    >
    > lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    > For i = lr To 1 Step -1
    > With ActiveSheet
    > If Cells(i, 11) = Cells(i + 1, 11) _
    > And Cells(i, 6) = Cells(i + 1, 6) _
    > And Cells(i, 4) = Cells(i + 1, 4) _
    > And Cells(i, 5) = Cells(i + 1, 5) Then
    > Cells(i, 12) = Cells(i, 3) + Cells(i + 1, 3)
    > End If
    > End With
    > Next
    >
    > This works if there is only one duplicate entry, but I can have multiple
    > duplicate entries. I need to sum all of the column C cells for each
    > duplicate found. Is this possible?
     
    Mike H, Dec 10, 2008
    #2
    1. Advertisements

  3. aileen

    aileen Guest

    I expect to see 16

    "Mike H" wrote:

    > Hi,
    >
    > I can see what your code is doing but am not clear about the result you
    > expect.
    >
    > take this data
    >
    > Columns
    > C D E F K L
    > 7 1 2 3 4 ?
    > 9 1 2 3 4
    >
    > Mike
    >
    >
    > What do you expect to see in column L
    >
    > Mike
    >
    >
    > "aileen" wrote:
    >
    > > I am checking for duplicates in a workbook. The following is the code I'm
    > > using:
    > >
    > > lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    > > For i = lr To 1 Step -1
    > > With ActiveSheet
    > > If Cells(i, 11) = Cells(i + 1, 11) _
    > > And Cells(i, 6) = Cells(i + 1, 6) _
    > > And Cells(i, 4) = Cells(i + 1, 4) _
    > > And Cells(i, 5) = Cells(i + 1, 5) Then
    > > Cells(i, 12) = Cells(i, 3) + Cells(i + 1, 3)
    > > End If
    > > End With
    > > Next
    > >
    > > This works if there is only one duplicate entry, but I can have multiple
    > > duplicate entries. I need to sum all of the column C cells for each
    > > duplicate found. Is this possible?
     
    aileen, Dec 10, 2008
    #3
  4. aileen

    aileen Guest

    Acutally, I need to see 16 in the first row, column L and it would be nice to
    be able to then delete the second row

    "Mike H" wrote:

    > Hi,
    >
    > I can see what your code is doing but am not clear about the result you
    > expect.
    >
    > take this data
    >
    > Columns
    > C D E F K L
    > 7 1 2 3 4 ?
    > 9 1 2 3 4
    >
    > Mike
    >
    >
    > What do you expect to see in column L
    >
    > Mike
    >
    >
    > "aileen" wrote:
    >
    > > I am checking for duplicates in a workbook. The following is the code I'm
    > > using:
    > >
    > > lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    > > For i = lr To 1 Step -1
    > > With ActiveSheet
    > > If Cells(i, 11) = Cells(i + 1, 11) _
    > > And Cells(i, 6) = Cells(i + 1, 6) _
    > > And Cells(i, 4) = Cells(i + 1, 4) _
    > > And Cells(i, 5) = Cells(i + 1, 5) Then
    > > Cells(i, 12) = Cells(i, 3) + Cells(i + 1, 3)
    > > End If
    > > End With
    > > Next
    > >
    > > This works if there is only one duplicate entry, but I can have multiple
    > > duplicate entries. I need to sum all of the column C cells for each
    > > duplicate found. Is this possible?
     
    aileen, Dec 10, 2008
    #4
  5. aileen

    Mike H Guest

    Aileen,

    But that's exactly what your code does for this data see the results I got
    Columns

    a b c d K L
    7 1 2 3 4 16
    9 1 2 3 4
    8 2 3 4 5 17
    9 2 3 4 5


    The only thing that confuses me is why are you setting the range with column 1
    wouldn't column 3 be a better option

    lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

    If you want to delete the rows without the totals add this to the end of
    your code

    lr = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
    For x = lr To 1 Step -1
    If Cells(x, "L").Value = "" Then
    Rows(x).EntireRow.Delete
    End If

    Mike
    "aileen" wrote:

    > Acutally, I need to see 16 in the first row, column L and it would be nice to
    > be able to then delete the second row
    >
    > "Mike H" wrote:
    >
    > > Hi,
    > >
    > > I can see what your code is doing but am not clear about the result you
    > > expect.
    > >
    > > take this data
    > >
    > > Columns
    > > C D E F K L
    > > 7 1 2 3 4 ?
    > > 9 1 2 3 4
    > >
    > > Mike
    > >
    > >
    > > What do you expect to see in column L
    > >
    > > Mike
    > >
    > >
    > > "aileen" wrote:
    > >
    > > > I am checking for duplicates in a workbook. The following is the code I'm
    > > > using:
    > > >
    > > > lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    > > > For i = lr To 1 Step -1
    > > > With ActiveSheet
    > > > If Cells(i, 11) = Cells(i + 1, 11) _
    > > > And Cells(i, 6) = Cells(i + 1, 6) _
    > > > And Cells(i, 4) = Cells(i + 1, 4) _
    > > > And Cells(i, 5) = Cells(i + 1, 5) Then
    > > > Cells(i, 12) = Cells(i, 3) + Cells(i + 1, 3)
    > > > End If
    > > > End With
    > > > Next
    > > >
    > > > This works if there is only one duplicate entry, but I can have multiple
    > > > duplicate entries. I need to sum all of the column C cells for each
    > > > duplicate found. Is this possible?
     
    Mike H, Dec 10, 2008
    #5
  6. aileen

    aileen Guest

    Yes, the code works when there is only one duplicate, but when I have more
    than one duplicate I need the original row to keep summing the column c.
    Here's an example.

    C A B D K L
    7 1 2 3 4 24
    8 1 2 3 4
    9 1 2 3 4
    7 1 2 3 5
    8 1 2 3 6 17
    9 1 2 3 6

    In this example I would like to delete rows 2,3, & 6, but not row 4 since it
    is not a duplicate. I hope this explains a little better what I am trying to
    accomplish.

    And thanks for your quick responses.


    Mke H" wrote:

    > Aileen,
    >
    > But that's exactly what your code does for this data see the results I got
    > Columns
    >
    > a b c d K L
    > 7 1 2 3 4 16
    > 9 1 2 3 4
    > 8 2 3 4 5 17
    > 9 2 3 4 5
    >
    >
    > The only thing that confuses me is why are you setting the range with column 1
    > wouldn't column 3 be a better option
    >
    > lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    >
    > If you want to delete the rows without the totals add this to the end of
    > your code
    >
    > lr = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
    > For x = lr To 1 Step -1
    > If Cells(x, "L").Value = "" Then
    > Rows(x).EntireRow.Delete
    > End If
    >
    > Mike
    > "aileen" wrote:
    >
    > > Acutally, I need to see 16 in the first row, column L and it would be nice to
    > > be able to then delete the second row
    > >
    > > "Mike H" wrote:
    > >
    > > > Hi,
    > > >
    > > > I can see what your code is doing but am not clear about the result you
    > > > expect.
    > > >
    > > > take this data
    > > >
    > > > Columns
    > > > C D E F K L
    > > > 7 1 2 3 4 ?
    > > > 9 1 2 3 4
    > > >
    > > > Mike
    > > >
    > > >
    > > > What do you expect to see in column L
    > > >
    > > > Mike
    > > >
    > > >
    > > > "aileen" wrote:
    > > >
    > > > > I am checking for duplicates in a workbook. The following is the code I'm
    > > > > using:
    > > > >
    > > > > lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    > > > > For i = lr To 1 Step -1
    > > > > With ActiveSheet
    > > > > If Cells(i, 11) = Cells(i + 1, 11) _
    > > > > And Cells(i, 6) = Cells(i + 1, 6) _
    > > > > And Cells(i, 4) = Cells(i + 1, 4) _
    > > > > And Cells(i, 5) = Cells(i + 1, 5) Then
    > > > > Cells(i, 12) = Cells(i, 3) + Cells(i + 1, 3)
    > > > > End If
    > > > > End With
    > > > > Next
    > > > >
    > > > > This works if there is only one duplicate entry, but I can have multiple
    > > > > duplicate entries. I need to sum all of the column C cells for each
    > > > > duplicate found. Is this possible?
     
    aileen, Dec 10, 2008
    #6
  7. aileen

    Mike H Guest

    Aileen,

    Now I understand. In practice I wouldn't have started from here had I
    understood the problem but having done that the code gets a bit messy but it
    seems to do what you want.

    Note that the last couple of lines delete the rows with no totals in. You
    will have to remove the comments to make them work. Don't do thta until your
    sure the code works

    Sub standard()
    lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    For i = 1 To lr

    If Cells(i, 11) = Cells(i + 1, 11) And Cells(i, 6) = Cells(i + 1, 6) _
    And Cells(i, 4) = Cells(i + 1, 4) _
    And Cells(i, 5) = Cells(i + 1, 5) Then
    mytotal = mytotal + Cells(i, 3).Value

    End If
    '***
    If Cells(i + 1, 11) <> Cells(i + 2, 11) And Cells(i + 1, 6) <> Cells(i +
    2, 6) _
    And Cells(i + 1, 4) <> Cells(i + 2, 4) _
    And Cells(i + 1, 5) <> Cells(i + 2, 5) Then

    Cells(i + 1, 12) = mytotal + Cells(i + 1, 3).Value
    mytotal = 0

    End If
    Next
    'deletes rows with not total in
    'lr = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
    'For x = lr To 1 Step -1
    ' If Cells(x, "L").Value = "" Then
    ' Rows(x).EntireRow.Delete
    ' End If
    'Next
    End Sub

    Mike

    "aileen" wrote:

    > Yes, the code works when there is only one duplicate, but when I have more
    > than one duplicate I need the original row to keep summing the column c.
    > Here's an example.
    >
    > C A B D K L
    > 7 1 2 3 4 24
    > 8 1 2 3 4
    > 9 1 2 3 4
    > 7 1 2 3 5
    > 8 1 2 3 6 17
    > 9 1 2 3 6
    >
    > In this example I would like to delete rows 2,3, & 6, but not row 4 since it
    > is not a duplicate. I hope this explains a little better what I am trying to
    > accomplish.
    >
    > And thanks for your quick responses.
    >
    >
    > Mke H" wrote:
    >
    > > Aileen,
    > >
    > > But that's exactly what your code does for this data see the results I got
    > > Columns
    > >
    > > a b c d K L
    > > 7 1 2 3 4 16
    > > 9 1 2 3 4
    > > 8 2 3 4 5 17
    > > 9 2 3 4 5
    > >
    > >
    > > The only thing that confuses me is why are you setting the range with column 1
    > > wouldn't column 3 be a better option
    > >
    > > lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    > >
    > > If you want to delete the rows without the totals add this to the end of
    > > your code
    > >
    > > lr = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
    > > For x = lr To 1 Step -1
    > > If Cells(x, "L").Value = "" Then
    > > Rows(x).EntireRow.Delete
    > > End If
    > >
    > > Mike
    > > "aileen" wrote:
    > >
    > > > Acutally, I need to see 16 in the first row, column L and it would be nice to
    > > > be able to then delete the second row
    > > >
    > > > "Mike H" wrote:
    > > >
    > > > > Hi,
    > > > >
    > > > > I can see what your code is doing but am not clear about the result you
    > > > > expect.
    > > > >
    > > > > take this data
    > > > >
    > > > > Columns
    > > > > C D E F K L
    > > > > 7 1 2 3 4 ?
    > > > > 9 1 2 3 4
    > > > >
    > > > > Mike
    > > > >
    > > > >
    > > > > What do you expect to see in column L
    > > > >
    > > > > Mike
    > > > >
    > > > >
    > > > > "aileen" wrote:
    > > > >
    > > > > > I am checking for duplicates in a workbook. The following is the code I'm
    > > > > > using:
    > > > > >
    > > > > > lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    > > > > > For i = lr To 1 Step -1
    > > > > > With ActiveSheet
    > > > > > If Cells(i, 11) = Cells(i + 1, 11) _
    > > > > > And Cells(i, 6) = Cells(i + 1, 6) _
    > > > > > And Cells(i, 4) = Cells(i + 1, 4) _
    > > > > > And Cells(i, 5) = Cells(i + 1, 5) Then
    > > > > > Cells(i, 12) = Cells(i, 3) + Cells(i + 1, 3)
    > > > > > End If
    > > > > > End With
    > > > > > Next
    > > > > >
    > > > > > This works if there is only one duplicate entry, but I can have multiple
    > > > > > duplicate entries. I need to sum all of the column C cells for each
    > > > > > duplicate found. Is this possible?
     
    Mike H, Dec 10, 2008
    #7
  8. aileen

    aileen Guest

    Mike,

    It's not quite doing what I need, but it's given me some ideas to work with.
    Thanks so much for all your help.

    Aileen

    "Mike H" wrote:

    > Aileen,
    >
    > Now I understand. In practice I wouldn't have started from here had I
    > understood the problem but having done that the code gets a bit messy but it
    > seems to do what you want.
    >
    > Note that the last couple of lines delete the rows with no totals in. You
    > will have to remove the comments to make them work. Don't do thta until your
    > sure the code works
    >
    > Sub standard()
    > lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    > For i = 1 To lr
    >
    > If Cells(i, 11) = Cells(i + 1, 11) And Cells(i, 6) = Cells(i + 1, 6) _
    > And Cells(i, 4) = Cells(i + 1, 4) _
    > And Cells(i, 5) = Cells(i + 1, 5) Then
    > mytotal = mytotal + Cells(i, 3).Value
    >
    > End If
    > '***
    > If Cells(i + 1, 11) <> Cells(i + 2, 11) And Cells(i + 1, 6) <> Cells(i +
    > 2, 6) _
    > And Cells(i + 1, 4) <> Cells(i + 2, 4) _
    > And Cells(i + 1, 5) <> Cells(i + 2, 5) Then
    >
    > Cells(i + 1, 12) = mytotal + Cells(i + 1, 3).Value
    > mytotal = 0
    >
    > End If
    > Next
    > 'deletes rows with not total in
    > 'lr = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
    > 'For x = lr To 1 Step -1
    > ' If Cells(x, "L").Value = "" Then
    > ' Rows(x).EntireRow.Delete
    > ' End If
    > 'Next
    > End Sub
    >
    > Mike
    >
    > "aileen" wrote:
    >
    > > Yes, the code works when there is only one duplicate, but when I have more
    > > than one duplicate I need the original row to keep summing the column c.
    > > Here's an example.
    > >
    > > C A B D K L
    > > 7 1 2 3 4 24
    > > 8 1 2 3 4
    > > 9 1 2 3 4
    > > 7 1 2 3 5
    > > 8 1 2 3 6 17
    > > 9 1 2 3 6
    > >
    > > In this example I would like to delete rows 2,3, & 6, but not row 4 since it
    > > is not a duplicate. I hope this explains a little better what I am trying to
    > > accomplish.
    > >
    > > And thanks for your quick responses.
    > >
    > >
    > > Mke H" wrote:
    > >
    > > > Aileen,
    > > >
    > > > But that's exactly what your code does for this data see the results I got
    > > > Columns
    > > >
    > > > a b c d K L
    > > > 7 1 2 3 4 16
    > > > 9 1 2 3 4
    > > > 8 2 3 4 5 17
    > > > 9 2 3 4 5
    > > >
    > > >
    > > > The only thing that confuses me is why are you setting the range with column 1
    > > > wouldn't column 3 be a better option
    > > >
    > > > lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    > > >
    > > > If you want to delete the rows without the totals add this to the end of
    > > > your code
    > > >
    > > > lr = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
    > > > For x = lr To 1 Step -1
    > > > If Cells(x, "L").Value = "" Then
    > > > Rows(x).EntireRow.Delete
    > > > End If
    > > >
    > > > Mike
    > > > "aileen" wrote:
    > > >
    > > > > Acutally, I need to see 16 in the first row, column L and it would be nice to
    > > > > be able to then delete the second row
    > > > >
    > > > > "Mike H" wrote:
    > > > >
    > > > > > Hi,
    > > > > >
    > > > > > I can see what your code is doing but am not clear about the result you
    > > > > > expect.
    > > > > >
    > > > > > take this data
    > > > > >
    > > > > > Columns
    > > > > > C D E F K L
    > > > > > 7 1 2 3 4 ?
    > > > > > 9 1 2 3 4
    > > > > >
    > > > > > Mike
    > > > > >
    > > > > >
    > > > > > What do you expect to see in column L
    > > > > >
    > > > > > Mike
    > > > > >
    > > > > >
    > > > > > "aileen" wrote:
    > > > > >
    > > > > > > I am checking for duplicates in a workbook. The following is the code I'm
    > > > > > > using:
    > > > > > >
    > > > > > > lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    > > > > > > For i = lr To 1 Step -1
    > > > > > > With ActiveSheet
    > > > > > > If Cells(i, 11) = Cells(i + 1, 11) _
    > > > > > > And Cells(i, 6) = Cells(i + 1, 6) _
    > > > > > > And Cells(i, 4) = Cells(i + 1, 4) _
    > > > > > > And Cells(i, 5) = Cells(i + 1, 5) Then
    > > > > > > Cells(i, 12) = Cells(i, 3) + Cells(i + 1, 3)
    > > > > > > End If
    > > > > > > End With
    > > > > > > Next
    > > > > > >
    > > > > > > This works if there is only one duplicate entry, but I can have multiple
    > > > > > > duplicate entries. I need to sum all of the column C cells for each
    > > > > > > duplicate found. Is this possible?
     
    aileen, Dec 11, 2008
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Marketneutral

    Sum cells until a value X is reaced and then start again

    Marketneutral, Mar 16, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    159
    Dick Kusleika
    Mar 16, 2005
  2. Guest
    Replies:
    0
    Views:
    189
    Guest
    Jun 6, 2006
  3. drdavidge
    Replies:
    3
    Views:
    384
    Guest
    Aug 8, 2006
  4. aileen

    Find duplicates, sum column then delete duplicates

    aileen, Dec 9, 2008, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    502
    aileen
    Dec 11, 2008
  5. ryguy7272

    Check for >0, then offset down 310, left 4, Check for = 0

    ryguy7272, Dec 9, 2009, in forum: Microsoft Excel Programming
    Replies:
    6
    Views:
    220
    ryguy7272
    Dec 9, 2009
Loading...

Share This Page