0
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
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