Transpose data from columns to rows
 

I need to find out how best to transpose the following:

A B C D

1 Jul-07 1 PRODUCT NAME 0
2 Aug-07 1 PRODUCT NAME 0
3 Sep-07 1 PRODUCT NAME 0
4 Oct-07 1 PRODUCT NAME 0
5 Nov-07 1 PRODUCT NAME 48
6 Dec-07 1 PRODUCT NAME 0
7 Jan-08 1 PRODUCT NAME 0
8 Feb-08 1 PRODUCT NAME 7
9 55
10
11 Jul-07 5 PRODUCT NAME 0
12 Aug-07 5 PRODUCT NAME 0
13 Sep-07 5 PRODUCT NAME 0
14 Oct-07 5 PRODUCT NAME 11
15 Nov-07 5 PRODUCT NAME 0
16 Dec-07 5 PRODUCT NAME 0
17 Jan-08 5 PRODUCT NAME 27
18 Feb-08 5 PRODUCT NAME 0
19 38

Jul-07 14 PRODUCT NAME 0
Aug-07 14 PRODUCT NAME 0
Sep-07 14 PRODUCT NAME 0
Oct-07 14 PRODUCT NAME 8
Nov-07 14 PRODUCT NAME 0
Dec-07 14 PRODUCT NAME 0
Jan-08 14 PRODUCT NAME 0
Feb-08 14 PRODUCT NAME 0
8

So that it looks like this:

A B C D E F
G H

Jul-07 Aug-07 Sep-07 Oct-07 Nov-07 Dec-07
Jan-08
1 1 0 0 0 48 0 0 7
2 5 0 0 0 11 0 0 27
3 14 0 0 0 8 0 0 0

(Need Feb 08 as well but wouldn't fit in this message)

Thank you
--
rb1

Transpose data from columns to rows
 

Try a pivot table, it only takes a couple of clicks, drag n drops to get the
transformations/analysis that you seek

In your data sheet,
Insert a new top row, then enter these (example) col headers in A1:D1
Mth, ID, Prod, Qty

Note: The "dates" in A2 down, eg: Jul-07, Aug-07, Sep-07, etc
are assumed 1st-of-month real dates

Select any cell within the table, click Data PivotTable ...
Click Next Next

In step 3 of the wizard, click Layout:
Drag n drop Mth into the COLUMN area
Drag n drop ID into the ROW area
Drag n drop Qty into the DATA area
(it'll appear as Sum of Qty)
Click OK Finish. That's it

Hop over to the pivot sheet (just to the left) for the results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rooboy" wrote:
I need to find out how best to transpose the following:

A B C D

1 Jul-07 1 PRODUCT NAME 0
2 Aug-07 1 PRODUCT NAME 0
3 Sep-07 1 PRODUCT NAME 0
4 Oct-07 1 PRODUCT NAME 0
5 Nov-07 1 PRODUCT NAME 48
6 Dec-07 1 PRODUCT NAME 0
7 Jan-08 1 PRODUCT NAME 0
8 Feb-08 1 PRODUCT NAME 7
9 55
10
11 Jul-07 5 PRODUCT NAME 0
12 Aug-07 5 PRODUCT NAME 0
13 Sep-07 5 PRODUCT NAME 0
14 Oct-07 5 PRODUCT NAME 11
15 Nov-07 5 PRODUCT NAME 0
16 Dec-07 5 PRODUCT NAME 0
17 Jan-08 5 PRODUCT NAME 27
18 Feb-08 5 PRODUCT NAME 0
19 38

Jul-07 14 PRODUCT NAME 0
Aug-07 14 PRODUCT NAME 0
Sep-07 14 PRODUCT NAME 0
Oct-07 14 PRODUCT NAME 8
Nov-07 14 PRODUCT NAME 0
Dec-07 14 PRODUCT NAME 0
Jan-08 14 PRODUCT NAME 0
Feb-08 14 PRODUCT NAME 0
8

So that it looks like this:

A B C D E F
G H

Jul-07 Aug-07 Sep-07 Oct-07 Nov-07 Dec-07
Jan-08
1 1 0 0 0 48 0 0 7
2 5 0 0 0 11 0 0 27
3 14 0 0 0 8 0 0 0

(Need Feb 08 as well but wouldn't fit in this message)

Thank you
--
rb1

Transpose data from columns to rows
 

Thanks Max, your solution did the trick.

Regards
--
rb1


"Max" wrote:

Try a pivot table, it only takes a couple of clicks, drag n drops to get the
transformations/analysis that you seek

In your data sheet,
Insert a new top row, then enter these (example) col headers in A1:D1
Mth, ID, Prod, Qty

Note: The "dates" in A2 down, eg: Jul-07, Aug-07, Sep-07, etc
are assumed 1st-of-month real dates

Select any cell within the table, click Data PivotTable ...
Click Next Next

In step 3 of the wizard, click Layout:
Drag n drop Mth into the COLUMN area
Drag n drop ID into the ROW area
Drag n drop Qty into the DATA area
(it'll appear as Sum of Qty)
Click OK Finish. That's it

Hop over to the pivot sheet (just to the left) for the results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rooboy" wrote:
I need to find out how best to transpose the following:

A B C D

1 Jul-07 1 PRODUCT NAME 0
2 Aug-07 1 PRODUCT NAME 0
3 Sep-07 1 PRODUCT NAME 0
4 Oct-07 1 PRODUCT NAME 0
5 Nov-07 1 PRODUCT NAME 48
6 Dec-07 1 PRODUCT NAME 0
7 Jan-08 1 PRODUCT NAME 0
8 Feb-08 1 PRODUCT NAME 7
9 55
10
11 Jul-07 5 PRODUCT NAME 0
12 Aug-07 5 PRODUCT NAME 0
13 Sep-07 5 PRODUCT NAME 0
14 Oct-07 5 PRODUCT NAME 11
15 Nov-07 5 PRODUCT NAME 0
16 Dec-07 5 PRODUCT NAME 0
17 Jan-08 5 PRODUCT NAME 27
18 Feb-08 5 PRODUCT NAME 0
19 38

Jul-07 14 PRODUCT NAME 0
Aug-07 14 PRODUCT NAME 0
Sep-07 14 PRODUCT NAME 0
Oct-07 14 PRODUCT NAME 8
Nov-07 14 PRODUCT NAME 0
Dec-07 14 PRODUCT NAME 0
Jan-08 14 PRODUCT NAME 0
Feb-08 14 PRODUCT NAME 0
8

So that it looks like this:

A B C D E F
G H

Jul-07 Aug-07 Sep-07 Oct-07 Nov-07 Dec-07
Jan-08
1 1 0 0 0 48 0 0 7
2 5 0 0 0 11 0 0 27
3 14 0 0 0 8 0 0 0

(Need Feb 08 as well but wouldn't fit in this message)

Thank you
--
rb1

Transpose data from columns to rows
 

Welcome. Glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rooboy" wrote in message
...
Thanks Max, your solution did the trick.

Regards
--
rb1