total duplicates, remove originals, and add to foot of spreadsheet

[nikkynock]

hi - i work with a lot of sales unit data, and need to total weekly sales
data of duplicates under one row name. For example, i would like to turn the
raw data from this:

A B C E F
product wk1 wk2 wk3 wk4
product a 2 3 4 5
product b 6 7 8 9
product a 10 11 1 13

..........into the following (with originals deleted)

A B C E F
product b 6 7 8 9
product a 12 14 5 18

would really appreciate your help

thanks

 

[Joel]

Try this macro

Sub CombineRows()

With ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data by column A
.Rows("1:" & LastRow).Sort _
key1:=.Range("A1"), _
Order1:=xlAscending, _
header:=xlYes

RowCount = 2
Do While .Range("A" & RowCount) <> ""
If .Range("A" & RowCount) = _
.Range("A" & (RowCount + 1)) Then

For ColCount = 2 To 5
.Cells(RowCount, ColCount) = _
.Cells(RowCount, ColCount) + .Cells(RowCount + 1, ColCount)
Next ColCount
.Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End With
End Sub

 

[nikkynock]

thanks Joel

that works a treat - i also have a data set where i want to match via values
rather than description and create one line of data (the values are unique to
the product, it's just that the product has a parent line) For example:

Value Description barcode column 1 column 2 (...to
column 12)
1234 Company 1 - sku 1 n/a 91011 121314
1234 Sku 1 5678 91011 121314

Becomes....

Value Description barcode column 1 column 2
1234 Company 1 - sku 1 5678 91011 121314

There are 12 columns of data that i would like this to apply to
many thanks

 

[Joel]

I made some minor changes to the old code and came up with a new macro

Sub CombineRowsDesc()

With ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'sort data by column A
.Rows("1:" & LastRow).Sort _
key1:=.Range("B1"), _
Order1:=xlAscending, _
header:=xlYes

RowCount = 2
Do While .Range("A" & RowCount) <> ""
If .Range("B" & RowCount) = _
.Range("B" & (RowCount + 1)) Then

For ColCount = 4 To 15
.Cells(RowCount, ColCount) = _
.Cells(RowCount, ColCount) + .Cells(RowCount + 1, ColCount)
Next ColCount

'Update Barcode
If Not IsNumeric(Range("C" & RowCount)) Then
Range("C" & RowCount) = Range("C" & (RowCount + 1))
End If

.Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End With
End Sub

 

[nikkynock]

Joel
your fast response is really appreciated - i've noticed that on some lines
where the parent value and sku value are different (generally VERY small
difference in value), the parent line is still removed. Am I doing it
correctly?
many thanks

 

[Joel]

The new code is using the description ONLY as the criteria for totaling and
delting. Do you want me to change the code so if the sku values are
differrent no to do the delete? I don't thing this make sense.


One solution is what I have done for lots of other people. A lot of people
don't do the deletes, instead they add a rows between sections a put a total
line to total each section. In your case you probably want to combine the
lines where the SKU numbers match using the original code. The sort by
description and add total lines between items with the same description but
different SKU numbers.