Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicates in a sheet
Hello, can anyone help with this?
I've got a table of data, basically it's rows with URLs (Col C) in it and stats such as how many "hits" (Col E) each received. What I need to do is search through the data and if any URLs are duplicates, I need to remove the duplicate row while adding up the total hits received for both rows and then writing this total back into Col E of the original row. This is what I have so far: Dim rCell As Range For Each rCell In Range("C1:C5000") sURL = rCell.Value iTotal = rCell.Offset(2, 0).Value MsgBox sURL, iTotal ' Now check to see if we have a duplicate iFlag = 1 'Range(Cells(BeginRow, BeginCol),Cells(L, col)) For Each rCheckCell In Range("C1:C5000") sCheck = rCheckCell.Value If sCheck = sURL Then ' We've got a match. Remove the duplicate row and add the figures before writing them. End If Next rCheckCell Next rCell End Sub I need the second loop to start at the row after the first loop, but I don't know how that's done, anyone got any ideas? If there's a better way to do this then please let me know! Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicates in a sheet
David,
Not extensively tested. Normally when deletring rows I would work backwards through the range Sub Stantial() Dim rCheckCell Dim rCell As Range For Each rCell In Range("C1:C6") sURL = rCell.Value itotal = rCell.Offset(, 2).Value MsgBox sURL & itotal ' Now check to see if we have a duplicate iFlag = 1 For Each rCheckCell In Range("C" & rCell.Row + 1 & ":C5000") sCheck = rCheckCell.Value If sCheck = sURL Then itotal = itotal + rCheckCell.Offset(, 2).Value rCell.Offset(, 2).Value = itotal rCheckCell.EntireRow.Delete End If Next rCheckCell Next rCell End Sub Mike "David" wrote: Hello, can anyone help with this? I've got a table of data, basically it's rows with URLs (Col C) in it and stats such as how many "hits" (Col E) each received. What I need to do is search through the data and if any URLs are duplicates, I need to remove the duplicate row while adding up the total hits received for both rows and then writing this total back into Col E of the original row. This is what I have so far: Dim rCell As Range For Each rCell In Range("C1:C5000") sURL = rCell.Value iTotal = rCell.Offset(2, 0).Value MsgBox sURL, iTotal ' Now check to see if we have a duplicate iFlag = 1 'Range(Cells(BeginRow, BeginCol),Cells(L, col)) For Each rCheckCell In Range("C1:C5000") sCheck = rCheckCell.Value If sCheck = sURL Then ' We've got a match. Remove the duplicate row and add the figures before writing them. End If Next rCheckCell Next rCell End Sub I need the second loop to start at the row after the first loop, but I don't know how that's done, anyone got any ideas? If there's a better way to do this then please let me know! Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicates in a sheet
Thanks Mike!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicates in a sheet
On Feb 17, 11:10*am, David wrote:
Thanks Mike! One more thing. If I wanted to simply search through all used rows, what changes would I need to make? The spreadsheets vary in size so always searching to row 5000 isn't efficient. Thanks again! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicates in a sheet
hi,
find the lastrow in col c Sub Stantial() Dim rCheckCell Dim rCell As Range lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row For Each rCell In Range("C1:C" & lastrow) sURL = rCell.Value itotal = rCell.Offset(, 2).Value MsgBox sURL & itotal ' Now check to see if we have a duplicate iFlag = 1 For Each rCheckCell In Range("C" & rCell.Row + 1 & ":C" & lastrow) sCheck = rCheckCell.Value If sCheck = sURL Then itotal = itotal + rCheckCell.Offset(, 2).Value rCell.Offset(, 2).Value = itotal rCheckCell.EntireRow.Delete End If Next rCheckCell Next rCell End Sub "David" wrote: On Feb 17, 11:10 am, David wrote: Thanks Mike! One more thing. If I wanted to simply search through all used rows, what changes would I need to make? The spreadsheets vary in size so always searching to row 5000 isn't efficient. Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging duplicate entries in Excel or deleting the duplicates (Exc | Excel Worksheet Functions | |||
merging sheet data | Excel Discussion (Misc queries) | |||
Merging From One Sheet to Another | Excel Discussion (Misc queries) | |||
Finding/deleting duplicates and merging cells | Excel Worksheet Functions | |||
Scanning for Duplicate Data in a Column, Merging Data and Finally Removing All Duplicates...? | Excel Programming |