Here is some code

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
Rows(i + 1).Resize(2).Insert
Cells(i + 1, "A").Value = Cells(i, "A").Value
Cells(i + 1, "B").Value = Range("C1").Value
Cells(i + 1, "C").Value = Cells(i, "C").Value
Cells(i + 2, "A").Value = Cells(i, "A").Value
Cells(i + 2, "B").Value = Range("D1").Value
Cells(i + 2, "C").Value = Cells(i, "D").Value
Cells(i, "C").Value = Cells(i, "B").Value
Cells(i, "B").Value = Range("B1").Value
Cells(i, "D").ClearContents
Next i
Rows(1).Delete
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bruce" <Bruce@discussions.microsoft.com> wrote in message
news:B342EC3C-4250-4793-AF85-90AE3250A4D7@microsoft.com...
> Assume the listing of data below:
>
>
> Num Name Tx Ok Ks
> 1 Smith 10 15 18
> 2 Jones 12 14 16
> 3 Brown 82 500 65
> 4 White 111 80 60
> 5 Pinkerton 75 65 55
>
>
> I need a method to transform as shown below:
>
> 1 Smith Tx 10
> 1 Smith Ok 15
> 1 Smith Ks 18
>
> and then continue for each subsequent name record above. Basically you are
> extracting the state column name and the value from that state's column
and
> creating another record.
>
> The desire and intent is not to use a macro. I figured a pivot table would
> accomplish that, but I haven't been able to figure out how yet.
>
> Thanks,
>
>
>
>
>
>
>