How to combine values from multiple rows into a single row using module?

I'm looking to combine multiple rows into a single row based on a value in the code column.. I've noticed other questions on here similiar to my question but I cannot seem to extend the range..

Code    Name    Value A Value B Value C Value D Value E
101   Example      #                
101   Example                       
101   Example                     #     
101   Example                            #  
101   Example                                    #
102   Example2                                   #
102   Example2                           #  
102   Example2                    #     
102   Example2            #         
102   Example2     #

So the final result looks like this:

Code    Name    Value A Value B Value C Value D Value E
101    Example     #               #       #       #
102    Example2    #       #       #       #       #

Edit

This is what I've got so far, my plan was to shift the items to the row above before deleting the entire row as one row may have many items.

Dim RowNum, LastRow, Col As Long

RowNum = 2
Col = 3

LastRow = Cells.SpecialCells(xlCellTypeLastCell).row
Range("A2", Cells(LastRow, 7)).Select

For Each row In Selection
    With Cells
        If Cells(RowNum, 1) = Cells(RowNum + 1, 1) Then
            For Each Cell In row
                If Cell > 0 Then
                Cells(RowNum + 1, Col).Copy Destination:=Cells(RowNum, Col)
                Else
                Col = Col + 1
            End If
        Rows(RowNum + 1).EntireRow.Delete
        End If
  End With

RowNum = RowNum + 1

Next row

damm

Posted 2014-01-30T12:08:28.327

Reputation: 15

1this solution will work. Start with it and see where you go wrong, then post what you have and we can help you where you're stuck. – Raystafarian – 2014-01-30T12:44:24.387

The solution works but with one issue, I'm not able to increment the ColumnIndex value with each loop: For Each Row In Selection With Cells If Cells(RowNum, 1) = Cells(RowNum + 1, 1) Then Cells(RowNum + 1, Col).Copy Destination:=Cells(RowNum, Col) 'Rows(RowNum + 1).EntireRow.Delete Col = Col + 1 End If End With – damm – 2014-01-30T14:12:21.530

1Work with the cells(RowNum, 1) - the "1" is the column number (A=1, B=2) and just copy what is happening. Like I said, write it up and post your code in your question and we'll be happy to troubleshoot it. – Raystafarian – 2014-01-30T14:26:55.440

Since there can be multiple items per row, will there ever be multiple items for matching rows in the same column? – Raystafarian – 2014-01-30T18:18:56.517

Apologies for the late reply,.Yes, there could be multiple items for matching rows in the same column – damm – 2014-01-31T09:24:22.967

That complicates things a bit. What about remaining in the position that they are in (value A stays as value A, etc)? Do they need to do that? If so, how to resolve conflicts? – Raystafarian – 2014-01-31T15:30:10.967

My plan is to overwrite the cell above with the cell in selection only if the cell in selection has a value in it.. I don't want empty cells to overwrite populated cells in the above. Once a row has no data in it (excluding columns A & B) then delete the entire row.. it doesn't need to deal with any conflicts – damm – 2014-01-31T16:33:09.907

How to combine values from multiple rows into a single row using module?

Answers