Combining rows with similar data
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 |
Combining rows with similar data
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" 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 |
Combining rows with similar data
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" wrote: 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" 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 |
Combining rows with similar data
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" 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. "Jim Cone" wrote: 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" 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 |
Combining rows with similar data
Thanks Jim, I will give it a try.
"Jim Cone" wrote: 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" 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. "Jim Cone" wrote: 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" 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 |
All times are GMT +1. The time now is 04:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com