My data looks like
data.png
My Macro turns the repeated rows of data for the last column into a delimited string
outputhave.png
I can get the macro to create delimited strings for both columns
outputneed.png
But in some projects, I might have 50 columns that I need to run this on
How to generalize this to work on n-Columns
Thanks
Sub GroupedRowsToDelimited Strings()
Dim v, r&, L&
With Worksheets("citClustered").UsedRange.Rows
ReDim v(1 To .count - 1, 1 To 3)
For r = 2 To .count
If .Cells(r, 1).value = .Cells(r - 1, 1).value Then
v(L, 3) = v(L, 3) & "|" & .Cells(r, 3).value
Else
If L Then v(L, 3) = v(L, 3)
L = L + 1
v(L, 1) = .Cells(r, 1).value
v(L, 2) = .Cells(r, 2).value
v(L, 3) = .Cells(r, 3).value
End If
Next
If L Then v(L, 3) = v(L, 3)
End With
Application.ScreenUpdating = False
With Worksheets("citGrouped")
.UsedRange.Offset(1).Clear
.[A2:C2].Resize(L).value = v
End With
Application.ScreenUpdating = True
End Sub
Bookmarks