Help me help a user in our office

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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)
 
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)
 
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
 
Bruce, here is an alternative using formulas.
Assumptions:
- The original table starts from A1 (headers).
- The columns I will present start from G2. (G1:K1) hold headers.

Column G:G: Aux. Start with the numbers 0, 1, 2, ... as far down as
necessary (i.e. 3*number of data rows - 1)
Column H:H: Num: In H2: =OFFSET($A$2,INT(G2/3),0)
Column I:I: Name: In I2: =VLOOKUP(H2,A:E,2,0)
Column J:J: State: In J2: =OFFSET($C$1,0,MOD(G2,3))
Column K:K: Value: In K2: =VLOOKUP(H2,A:E,MATCH(J2,$A$1:$E$1,0),0)

HTH
Kostis Vezerides
 
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)
 
Back
Top