# Check for Duplicates then Sum cells of duplicates

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

1. ### aileenGuest

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

2. ### Mike HGuest

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

3. ### aileenGuest

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
4. ### aileenGuest

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
5. ### Mike HGuest

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

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
6. ### aileenGuest

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
>
> 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
7. ### Mike HGuest

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
> >
> > 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
8. ### aileenGuest

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
> > >
> > > 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