Change Vertical Table of values to Horizontal table

[Steve]

Can someone point me in the right direction here?

I have a table with 5 columns in it

COL A is Social Security Number
COL B is Value1
COL C is Value2
COL D is Value3
COL E is Value4

There are varying numbers of rows for any given social security nmber
- 5 rows for SSN1, 2 rows for SSN2, 7 rows for SSN3, etc.

Each row has Value1, Value2, Value3, and Value4

Also, the values of Value1, Value2, Value3, and Value4 are all
different.

My task is to convert this table to a horizontal table - the number of
rows to equal the number of unique Social Security Numbers.

The number of columns will be the number of unique social security
numbers multiplied by 4 plus one.

So the First rows' columns will be SSN, Value1, Value2, Value3,
Value4, Value1(from the second row in the Vertical Table), Value2(from
the second row in the Vertical Table), Value3(from the second row in
the Vertical Table), Value4(from the second row in the Vertical
Table), Value1(from the third row in the vertical table), Value2(from
the third row in the vertical table),etc.

Has anyone out there done this or know where I can find an example of
it being done?
I've looked at all of the manuals I can get my hands on and I have had
no luck.

If I only have 5 Social Security Numbers to work with, I can type each
cell reference.
I unfortunately have several thousand to deal with.

Many thanks!

steve

 

[JE McGimpsey]

One way is to use a macro:

Public Sub ConsolidateSSNs()
Dim rDest As Range
Dim rSource As Range
Dim rCell As Range
Dim sOld As String

With ActiveSheet
Set rSource = .Range("A1:A" & _
.Range("A" & .Rows.Count).End(xlUp).Row)
End With
Set rDest = Worksheets.Add(After:=ActiveSheet).Range("B1")
With rSource
sOld = .Item(1).Text
rDest.Offset(0, -1).Resize(1, 5).Value = .Resize(1, 5).Value
For Each rCell In .Offset(1, 0).Resize(.Rows.Count - 1, 1)
With rCell
If .Text = sOld Then
Set rDest = rDest.Offset(0, 4)
Else
With rDest.Parent.Cells(rDest.Row + 1, 1)
sOld = rCell.Text
.Value = sOld
Set rDest = .Offset(0, 1)
End With
End If
rDest.Resize(1, 4).Value = _
.Offset(0, 1).Resize(1, 4).Value
End With
Next rCell
End With
End Sub