How to combine values from multiple rows into a single row in Excel?
Solution 1
This is VBA, or a macro you can run on your sheet. You must hit alt+F11 to bring up the Visual Basic for Application prompt, go to your workbook and right click - insert - module
and paste this code in there. You can then run the module from within VBA by pressing F5. This macro is named "test"
Sub test()
'define variables
Dim RowNum as long, LastRow As long
'turn off screen updating
Application.ScreenUpdating = False
'start below titles and make full selection of data
RowNum = 2
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Range("A2", Cells(LastRow, 4)).Select
'For loop for all rows in selection with cells
For Each Row In Selection
With Cells
'if customer name matches
If Cells(RowNum, 1) = Cells(RowNum + 1, 1) Then
'and if customer year matches
If Cells(RowNum, 4) = Cells(RowNum + 1, 4) Then
'move attribute 2 up next to attribute 1 and delete empty line
Cells(RowNum + 1, 3).Copy Destination:=Cells(RowNum, 3)
Rows(RowNum + 1).EntireRow.Delete
End If
End If
End With
'increase rownum for next test
RowNum = RowNum + 1
Next Row
'turn on screen updating
Application.ScreenUpdating = True
End Sub
This will run through a sorted spreadsheet and combine consecutive rows that match both the customer and the year and delete the now empty row. The spreadsheet must be sorted the way you've presented it, customers and years ascending, this particular macro won't look beyond consecutive rows.
Edit - it's entirely possible my with statement
is completely unneeded, but it's not hurting anyone..
REVISITED 02/28/14
Someone used this answer in another question and when I went back I thought this VBA poor. I've redone it -
Sub CombineRowsRevisited()
Dim c As Range
Dim i As Integer
For Each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))
If c = c.Offset(1) And c.Offset(,4) = c.Offset(1,4) Then
c.Offset(,3) = c.Offset(1,3)
c.Offset(1).EntireRow.Delete
End If
Next
End Sub
Revisited 05/04/16
Asked again How to combine values from multiple rows into a single row? Have a module, but need the variables explaining and again, it's pretty poor.
Sub CombineRowsRevisitedAgain()
Dim myCell As Range
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For Each myCell In Range(Cells("A2"), Cells(lastRow, 1))
If (myCell = myCell.Offset(1)) And (myCell.Offset(0, 4) = myCell.Offset(1, 4)) Then
myCell.Offset(0, 3) = myCell.Offset(1, 3)
myCell.Offset(1).EntireRow.Delete
End If
Next
End Sub
However, depending on the problem, it might be better to step -1
on a row number so nothing gets skipped.
Sub CombineRowsRevisitedStep()
Dim currentRow As Long
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For currentRow = lastRow To 2 Step -1
If Cells(currentRow, 1) = Cells(currentRow - 1, 1) And _
Cells(currentRow, 4) = Cells(currentRow - 1, 4) Then
Cells(currentRow - 1, 3) = Cells(currentRow, 3)
Rows(currentRow).EntireRow.Delete
End If
Next
End Sub
Solution 2
Another option:
- Select Column B, press CTRL+G ->
Special
->Blanks
->OK
- Type
=
press ↑, then CTRL+ENTER - Select Column C, press CTRL+G ->
Special
->Blanks
->OK
- Type
=IF(
press ← type=
press ← press ↓ type,
press ↓ type,0)
then CTRL+ENTER - Select all data and
Copy
andPaste Special as Values
- Remove duplicates.
EDIT:
Explanation: I am trying to make use of GoTo Blanks option. Once you have selected blanks then you can enter the same formula for all selected blank cells. Regarding OP's question, the data looks like have consistent blanks, i.e.: Value A
column blanks have the same CustomerID
as above nonblank row. In the same way Value B
column blanks have the same CustomerID
as below nonblank row.
Related videos on Youtube
Greg R.
Updated on September 18, 2022Comments
-
Greg R. over 1 year
I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this:
I'm stuck with a row for Customer 1 for Value A in 2009 and a separate row for Value B for the same customer in the same year.
In some instances there is no Value A or Value B. In the example above, you can see that Customer 1 has no Value B in 2011, so no row was generated for that. And, though not represented in the example, some clients will have no data for either value in a year (and thus no row for that customer in that year). In that situation, lacking a row for that customer in that year is fine.
I want to get this into a worksheet where there's one row for both values for every year and customer. I.e., I want the data to look like this:
What is the most effective way to create that result?
-
xXPhenom22Xx about 12 yearsYou will run into issues though with cells that have no value in column B because Excel will have no way of knowing that the value in B2 and B3 are in someway related.
-
Greg R. about 12 yearsI admittedly have little experience with Pivot Tables, but I don't think that Pivot Tables solve the problem I have. I want the data for all the values for a customer in one row per year so I can manipulate it (e.g. create a column adding Value A + Value B), not just change how I'm viewing it via a Pivot Table. I literally want the data structured differently in the worksheet.
-
xXPhenom22Xx about 12 yearsYea would be tough unless you always had alternating columns with missing data like you do in the example. If this isnt the case then there isnt a way for Excel to logically do what you want it too.
-
nixda almost 11 yearsHello zx8754. Could you please add some explanations what the idea behind your solution is?
-
zx8754 almost 11 years@nixda Explanation added, sorry thought the steps were clear enough.