I want to take select cells from other rows (2 and 3) and combinethem with row 1

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!

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