Combining rows with similar data

[Guest]

I have a list of data in rows with several rows being more or less a
duplicate of each other apart from some figures in one column. I want to be
able to add the figures together and end up with only one row for each set of
data. An added complication is that the data changes daily!!

e.g.
Data I have is:
Ref Name Amount
23 John 20.00
38 Steve 10.00
38 Steve 15.00

Result I need is:
Ref Name Amount
23 John 20.00
38 Steve 25.00

Thanks

 

[Jim Cone]

Try using the Excel "Subtotals" feature on the Data menu.
If your data is sorted then it will do what you want.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"robertlewis" <[email protected]>
wrote in message
I have a list of data in rows with several rows being more or less a
duplicate of each other apart from some figures in one column. I want to be
able to add the figures together and end up with only one row for each set of
data. An added complication is that the data changes daily!!

e.g.
Data I have is:
Ref Name Amount
23 John 20.00
38 Steve 10.00
38 Steve 15.00

Result I need is:
Ref Name Amount
23 John 20.00
38 Steve 25.00

Thanks

 

[Guest]

Thanks Jim
I have tried subtotals but it does not leave me with a complete row of data,
just the subtotals. My example is simplified but the actual spreadsheet has
23 columns of data.

 

[Jim Cone]

This isn't perfect, but as they say it is a start.
'You must specify the column containing the title for the subtotals
'and you must specify the column containing the totals.
'Select a cell in the subtotal range and then run the sub.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'Adds text from the row above to each subtotaled row.
Function FillSubTotalBlanks(ByRef rngCell As Range, _
ByRef lngFirst As Long, ByRef lngLast As Long)
Dim rng As Range
Dim lngRow As Long
Dim strItem As String
Dim dblTotal As Double
Set rng = rngCell.CurrentRegion
For lngRow = 1 To rng.Rows.Count - 1
If InStr(1, rng.Cells(lngRow, lngLast).Formula, _
"SUBTOTAL", vbTextCompare) > 0 Then
strItem = rng.Cells(lngRow, lngFirst).Text
dblTotal = rng.Cells(lngRow, lngLast).Value2
rng.Rows(lngRow).Value = rng.Rows(lngRow - 1).Value
rng.Cells(lngRow, lngFirst).Value = strItem
rng.Cells(lngRow, lngLast).Value = dblTotal
strItem = vbNullString
dblTotal = 0
End If
Next 'lngRow
Set rng = Nothing
Set rngCell = Nothing
End Function
'--
'This sub calls the FillSubTotalBlanks function.
Sub FillInTheBlanks()
Dim lngTitleColumn As Long
Dim lngTotalColumn As Long
lngTitleColumn = 4 '<<< Use correct column number
lngTotalColumn = 7 '<<< Use correct column number
'Call Function
FillSubTotalBlanks ActiveCell, lngTitleColumn, lngTotalColumn
End Sub
'-----------------



"robertlewis"
<[email protected]>
wrote in message
Thanks Jim
I have tried subtotals but it does not leave me with a complete row of data,
just the subtotals. My example is simplified but the actual spreadsheet has
23 columns of data.

 

[Guest]

Thanks Jim, I will give it a try.

This isn't perfect, but as they say it is a start.
'You must specify the column containing the title for the subtotals
'and you must specify the column containing the totals.
'Select a cell in the subtotal range and then run the sub.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'Adds text from the row above to each subtotaled row.
Function FillSubTotalBlanks(ByRef rngCell As Range, _
ByRef lngFirst As Long, ByRef lngLast As Long)
Dim rng As Range
Dim lngRow As Long
Dim strItem As String
Dim dblTotal As Double
Set rng = rngCell.CurrentRegion
For lngRow = 1 To rng.Rows.Count - 1
If InStr(1, rng.Cells(lngRow, lngLast).Formula, _
"SUBTOTAL", vbTextCompare) > 0 Then
strItem = rng.Cells(lngRow, lngFirst).Text
dblTotal = rng.Cells(lngRow, lngLast).Value2
rng.Rows(lngRow).Value = rng.Rows(lngRow - 1).Value
rng.Cells(lngRow, lngFirst).Value = strItem
rng.Cells(lngRow, lngLast).Value = dblTotal
strItem = vbNullString
dblTotal = 0
End If
Next 'lngRow
Set rng = Nothing
Set rngCell = Nothing
End Function
'--
'This sub calls the FillSubTotalBlanks function.
Sub FillInTheBlanks()
Dim lngTitleColumn As Long
Dim lngTotalColumn As Long
lngTitleColumn = 4 '<<< Use correct column number
lngTotalColumn = 7 '<<< Use correct column number
'Call Function
FillSubTotalBlanks ActiveCell, lngTitleColumn, lngTotalColumn
End Sub
'-----------------



"robertlewis"
<[email protected]>
wrote in message
Thanks Jim
I have tried subtotals but it does not leave me with a complete row of data,
just the subtotals. My example is simplified but the actual spreadsheet has
23 columns of data.