Convert Rows data

[Juan]

Hi there,
I have the following sample data:
PARTNUMBER QTY RATe PRUN VALID_FROM_VALID_TO
ZWR1 0.1095 USD 1 20050512 20050526
XREZ12 0.1095 USD 1 20050527 99991231

is it possible to put this data in the following:

20050512 20050526 20050527 99991231
ZWR1 XREZ12
0.1095 0.1095
USD USD
1 1

So basically make the existing Row heading as Column heading, Please advise
if this can be done. Been trying to do with pivot but can't seem to get it to
work.

Really appreciate any help.
Thank YOU,
J

[Max]

One interp, and play (assume you want it dynamic) ..

Assuming sample table is in Sheet1,
in A1:E3, data from row2 down,
where E1:E3 houses:

VALID_FROM_VALID_TO
20050512 20050526
20050527 99991231

In a new Sheet2
-----------
Put in A2:

=OFFSET(Sheet1!$E$1,COLUMNS($A$1:A1),)

Copy A2 across as many cols as there are data rows in col E in Sheet1.

For the sample data which is 2 rows, copy A2 across to B2.

Note that the max transposable data rows from Sheet1 will be 256, i.e. the
max # of columns available per sheet

Put in A3:
=OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1),ROWS($A$1:A1)-1)

Copy A3 across as many cols as was done for A2,
fill down by by another 3 rows

For the sample data, copy A3 across to B3, fill down to B6
You should get the desired result in A3:B6

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Juan" <Juan@discussions.microsoft.com> wrote in message
news:EE13788C-F34E-4C86-A7E9-A08BA232CC6D@microsoft.com...
> Hi there,
> I have the following sample data:
> PARTNUMBER QTY RATe PRUN VALID_FROM_VALID_TO
> ZWR1 0.1095 USD 1 20050512 20050526
> XREZ12 0.1095 USD 1 20050527 99991231
>
> is it possible to put this data in the following:
>
> 20050512 20050526 20050527 99991231
> ZWR1 XREZ12
> 0.1095 0.1095
> USD USD
> 1 1
>
> So basically make the existing Row heading as Column heading, Please
advise
> if this can be done. Been trying to do with pivot but can't seem to get it
to
> work.
>
> Really appreciate any help.
> Thank YOU,
> J

[Juan]

Hello Max,
sorry for the late reply. Just wanted to say thanks for your help. This
should do the job.

Really appreciated.

juan

"Max" wrote:

> One interp, and play (assume you want it dynamic) ..
>
> Assuming sample table is in Sheet1,
> in A1:E3, data from row2 down,
> where E1:E3 houses:
>
> VALID_FROM_VALID_TO
> 20050512 20050526
> 20050527 99991231
>
> In a new Sheet2
> -----------
> Put in A2:
>
> =OFFSET(Sheet1!$E$1,COLUMNS($A$1:A1),)
>
> Copy A2 across as many cols as there are data rows in col E in Sheet1.
>
> For the sample data which is 2 rows, copy A2 across to B2.
>
> Note that the max transposable data rows from Sheet1 will be 256, i.e. the
> max # of columns available per sheet
>
> Put in A3:
> =OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1),ROWS($A$1:A1)-1)
>
> Copy A3 across as many cols as was done for A2,
> fill down by by another 3 rows
>
> For the sample data, copy A3 across to B3, fill down to B6
> You should get the desired result in A3:B6
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Juan" <Juan@discussions.microsoft.com> wrote in message
> news:EE13788C-F34E-4C86-A7E9-A08BA232CC6D@microsoft.com...
> > Hi there,
> > I have the following sample data:
> > PARTNUMBER QTY RATe PRUN VALID_FROM_VALID_TO
> > ZWR1 0.1095 USD 1 20050512 20050526
> > XREZ12 0.1095 USD 1 20050527 99991231
> >
> > is it possible to put this data in the following:
> >
> > 20050512 20050526 20050527 99991231
> > ZWR1 XREZ12
> > 0.1095 0.1095
> > USD USD
> > 1 1
> >
> > So basically make the existing Row heading as Column heading, Please
> advise
> > if this can be done. Been trying to do with pivot but can't seem to get it
> to
> > work.
> >
> > Really appreciate any help.
> > Thank YOU,
> > J
>
>
>

[Max]

Pleased to hear that !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Juan" <Juan@discussions.microsoft.com> wrote in message
news:F09B6CAC-4EB3-4911-9976-A6515CE58282@microsoft.com...
> Hello Max,
> sorry for the late reply. Just wanted to say thanks for your help. This
> should do the job.
>
> Really appreciated.
>
> juan