On Monday, August 6, 2012 3:53:31 PM UTC-5, (unknown) wrote:
I have this data in my spread sheet



Header- MemberID, Plan, Coverage Type, Premium

Row 1 - ABC, Health, IND, $100

Row 2 - ABC, Dental, FAM, $50

Row 3 - ABC, Vision, FAM, $25



Is there a way I can combine the rows to get this:

Header - MemberID, Plan, Coverage Type, Premium, Plan, Coverage Type, Premium, Plan, Coverage Type, Premium

Row 1 - ABC, Health, IND, $100, Dental, FAM, $50, Vision, FAM, $25





I have multiple memebers in my spreadsheet and the data all runs vertically and I want to combine it and go horizontal.



Any help is greatly appreciated!

Try this macro

Sub putonrowsSAS()
Dim i As Long
Dim lc As Long

For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
lc = Cells(i, Columns.Count).End(xlToLeft).Column + 1
If Cells(i + 1, 1) = Cells(i, 1) Then
Cells(i + 1, 2).Resize(, 200).Copy Cells(i, lc)
Rows(i + 1).Delete
End If
Next i
End Sub