# Check for Duplicates then Sum cells of duplicates

aileen
Guest
Posts: n/a

 10th Dec 2008
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
Guest
Posts: n/a

 10th Dec 2008
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
Guest
Posts: n/a

 10th Dec 2008
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
Guest
Posts: n/a

 10th Dec 2008
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
Guest
Posts: n/a

 10th Dec 2008
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
Guest
Posts: n/a

 10th Dec 2008
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
Guest
Posts: n/a

 10th Dec 2008
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
Guest
Posts: n/a

 11th Dec 2008
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?

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post cwoelfe Microsoft Access 1 10th Sep 2009 06:20 PM aileen Microsoft Excel Programming 3 11th Dec 2008 06:03 PM Weissme Microsoft Excel Misc 0 9th Aug 2006 04:35 PM =?Utf-8?B?Ti5SLg==?= Microsoft Excel Worksheet Functions 2 23rd Jun 2005 06:14 PM Ralph Malph Windows XP General 2 9th Feb 2005 07:05 AM

Features