How to combine values from multiple rows into a single row? Have a module, but need the variables explaining

Question

I'm completely new to VBA and trying to implement the excellent looking solution here by Raystafarian How to combine values from multiple rows into a single row in Excel? (the revisited version). However as I really don't understand the solution, I'm struggling to run it where the column numbers, etc, are different. Please could someone explain to me what the variables refer to so that I can apply it to a different example? My problem is exactly the same - multiple rows for a single datapoint and looking to get a table with name/data1/data2/data3 all in the same row for Spotfire data analysis.

score 1 · Answer 1 · edited Jun 24 '20 at 10:51

I suggest a different way as VBA to handle this transformation step.

You may try the free Microsoft Add-In Power Query (from Excel 2010). It's quite intuitive for Loading and transforming your data. Basically you import the datas in Power Query, unpivot and repivot them.

First define your data as a table in Excel.

| Customer   | Value A | Value B | Year |
|------------|---------|---------|------|
| Customer 1 | 134     |         | 2009 |
| Customer 1 |         | 3       | 2009 |
| Customer 1 | 175     |         | 2010 |
| Customer 1 |         | 5       | 2010 |
| Customer 1 | 7784    |         | 2011 |
| Customer 2 | 515     |         | 2009 |
| Customer 2 | 1943    |         | 2010 |
| Customer 2 |         | 1       | 2010 |
| Customer 2 | 9745    |         | 2011 |
| Customer 2 |         | 154     | 2011 |

Put the cursor somewhere in the table

Go to the Power Query tab and click From Table. It will open the Power Query Editor.

Select the Value A/B columns (with Ctrl). Right-click one of the column headings you’ve selected to pull up an options menu. Click the Unpivot Columns menu.

Next Step: Select the Attribute and Value columns and then select Pivot Column from the Transform menu. Choose the Value column to Pivot from the dropdown and click Ok.

The Output will look like this:

| Customer   | Year | Value A | Value B |
|------------|------|---------|---------|
| Customer 1 | 2009 | 134     | 3       |
| Customer 1 | 2010 | 175     | 5       |
| Customer 1 | 2011 | 7784    |         |
| Customer 2 | 2009 | 515     |         |
| Customer 2 | 2010 | 1943    | 1       |
| Customer 2 | 2011 | 9745    | 154     |

The Power Query script of this three steps is

let
    Source= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Customer", "Year"}, "Attribut", "Value"),
    #"Pivot Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribut]), "Attribut", "Value", List.Sum)
in
    #"Pivot Column"

You can also find a step by step tutorial about unpivoting here

score 0 · Answer 2 · answered May 04 '16 at 10:52

These are the explanations for the most important lines of the script:

For each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))

Iterate through all the column A from A2 to the last used cell, assigning the value to variable c.

As cis a cell, it has a row and column value, so it acts as a point of reference from where we can move using Offset.

If c = c.Offset(1) And c.Offset(,4) = c.Offset(1,4) Then

Compare cell c with cell on next row c.Offset(1) AND the cell on the same row but in column 4 c.Offset(,4) with the cell on the following row of column 4 c.Offset(1,4).

c.Offset(,3) = c.Offset(1,3)

Assign to the cell on the row and column 3 c.Offset(,3) the value of the cell on the next row and column 3 c.Offset(1,3).

c.Offset(1).EntireRow.Delete

Deletes the next row

Raystafarian · Accepted Answer · 2016-05-04T11:14:49.277

It's a pretty rough macro but

Option Explicit

Sub CombineRowsRevisited()

'c is a CELL or a range
Dim c As Range
'i is a number
Dim i As Integer

'for each CELL in this range
For Each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))
'if the CELL is the same as the cell to the right AND
'if the cell 4 to the right is the same as the cell below that one
If c = c.Offset(1) And c.Offset(, 4) = c.Offset(1, 4) Then
            'then make the cell 3 to the right the same as the cell below it
            c.Offset(, 3) = c.Offset(1, 3)
            'and delete the row below the CELL
            c.Offset(1).EntireRow.Delete
End If

Next

End Sub

This would be easier to understand given the above

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

How to combine values from multiple rows into a single row? Have a module, but need the variables explaining

3 Answers3

Linked