I am in need of a formula or macro that would help me consolidate data into a cell in a different sheet. I thought I could use VLOOKUP, but I have not had any luck.
Thanks all,
Thanks all,
Sub CombineText() Dim x, y(), i As Long With ActiveSheet x = .Cells(1).CurrentRegion For i = 1 To UBound(x, 1) iii = iii + 1: ReDim Preserve y(1 To 2, 1 To iii) If i = UBound(x, 1) Then y(1, iii) = x(i, 1): y(2, iii) = x(i, 2) ElseIf x(i, 1) = x(i + 1, 1) Then ii = i Do Until x(ii, 1) <> x(i, 1) If ii = i Then y(1, iii) = x(ii, 1): y(2, iii) = x(ii, 2) Else y(1, iii) = x(ii, 1): y(2, iii) = y(2, iii) & vbLf & x(ii, 2) End If ii = ii + 1 If ii = UBound(x, 1) + 1 Then Exit For Loop i = ii - 1 Else y(1, iii) = x(i, 1): y(2, iii) = x(i, 2) End If Next Application.ScreenUpdating = 0 .Columns(1).Resize(, 2).Clear With .[a1] .Resize(iii, 2) = Application.Transpose(y) .CurrentRegion.VerticalAlignment = -4108 .CurrentRegion.HorizontalAlignment = -4108 End With End With End Sub
Comment