Help me help a user in our office
Bruce,
Here is an alternative formula solution
Assuming this data is on Sheet1, then on Sheet2
cell A1: =INDIRECT("Sheet3!A"&(INT((ROW()-1)/3)+2))
cell B1: =INDEX(Sheet3!$B$1:$D$1,,MOD(ROW()-1,3)+1)
cell C1:
=INDEX(INDIRECT("Sheet3!$B"&INT((ROW()-1)/3)+2&":$D"&INT((ROW()-1)/3)+2),,MO
D(ROW()-1,3)+1)
anjd copy down until it goes bad
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Bruce" wrote in message
...
Ok Bob, I'll fiddle with the code to see how it works, but my user will be
skiddish about macro use. Do you not think what I describe can be done
without code?
Bruce
"Bob Phillips" wrote:
Here is some code
Sub Test()
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" wrote in message
...
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,
|