Help me help a user in our office

[Bruce]

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,

[Bob Phillips]

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,
>
>
>
>
>
>
>

[Bob Phillips]

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" <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,
>
>
>
>
>
>
>

[Bruce]

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" <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,
> >
> >
> >
> >
> >
> >
> >
>
>
>
>

[vezerid]

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

[Bob Phillips]

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" <Bruce@discussions.microsoft.com> wrote in message
news:4245DC08-60E1-49E0-BFCE-498AE08EF041@microsoft.com...
> 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" <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,
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
> >