PC Review


Reply
Thread Tools Rate Thread

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?
 
Reply With Quote
 
 
 
 
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?

 
Reply With Quote
 
 
 
 
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?

 
Reply With Quote
 
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?

 
Reply With Quote
 
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
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?

 
Reply With Quote
 
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
> 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?

 
Reply With Quote
 
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
> > 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?

 
Reply With Quote
 
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
> > > 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?

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help to Count in Footer, then Sum in 2nd Footer then Sum agai cwoelfe Microsoft Access 1 10th Sep 2009 06:20 PM
Find duplicates, sum column then delete duplicates aileen Microsoft Excel Programming 3 11th Dec 2008 06:03 PM
check for duplicates, then sum unique values Weissme Microsoft Excel Misc 0 9th Aug 2006 04:35 PM
if greater then 99 then 1 if greater then 199 then two =?Utf-8?B?Ti5SLg==?= Microsoft Excel Worksheet Functions 2 23rd Jun 2005 06:14 PM
Need help with a log off/log on problem...can log off then log on dif. user and system will be slow loading user settings, then play start up music, then show wallpaper, then freeze. Ralph Malph Windows XP General 2 9th Feb 2005 07:05 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:25 AM.