PDA

View Full Version : Solved: Shift rows to columns by condition



Shanmugam
04-29-2009, 08:45 AM
Hi,

I am having multiple rows which i need to shift to columns.

Attached is the sample file. There i have given example for source and the output data. Is this possible to do this output data through VBA or is there any excel formula available to do this?

Thanks,
Shanmugam

Bob Phillips
04-29-2009, 09:01 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 3 Step -1

If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then

.Cells(i - 1, "B").Value = .Cells(i - 1, "B").Value & "," & .Cells(i, "B").Value
.Rows(i).Delete
End If
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

Shanmugam
04-29-2009, 09:33 AM
It worked. Thanks much for this.