PIVOT TABLE - Summary Table into a Databasae Table.

[sansk_23]

If i have a Summary Sheet.1 like

Prod ID / Product Name / Jan / Feb / Mar / Apr / May / June -------
123 Book A 20 30 40 50 60 70
234 Book B 10 20 40 60 30 50

How do i get the Database Table as -
Prod ID / Prod Name / Month / Qty
123 Book A Jan 20
123 Book A Feb 30
123 Book A Mar 40
and so on & so forth.....


AND if i have a similar Summary Sheet having the Product wise Value/Price in
Sheet.2,

how do i have the Database Table as
Prod ID / Prod Name / Month / Value , where value = Sheet.1-Qty x
Sheet.2-Value.

Pls. help

[thecuzin]

That is basicaly the reverse of a pivot table. I would love to know. In MS
Access you could use a union query but I am stumped in excel.

"sansk_23" wrote:

> If i have a Summary Sheet.1 like
>
> Prod ID / Product Name / Jan / Feb / Mar / Apr / May / June -------
> 123 Book A 20 30 40 50 60 70
> 234 Book B 10 20 40 60 30 50
>
> How do i get the Database Table as -
> Prod ID / Prod Name / Month / Qty
> 123 Book A Jan 20
> 123 Book A Feb 30
> 123 Book A Mar 40
> and so on & so forth.....
>
>
> AND if i have a similar Summary Sheet having the Product wise Value/Price in
> Sheet.2,
>
> how do i have the Database Table as
> Prod ID / Prod Name / Month / Value , where value = Sheet.1-Qty x
> Sheet.2-Value.
>
> Pls. help

[Debra Dalgleish]

To reorganize the data, you can use the "unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Because you have two label columns, you should concatenate them, before
using the above technique. For example, insert a blank column after
Product Name, with a heading, and the formula:

=A2 & "$" & B2

After using the technique, use Data>Text to Columns, Delimited, to split
the data into separate columns.


sansk_23 wrote:
> If i have a Summary Sheet.1 like
>
> Prod ID / Product Name / Jan / Feb / Mar / Apr / May / June -------
> 123 Book A 20 30 40 50 60 70
> 234 Book B 10 20 40 60 30 50
>
> How do i get the Database Table as -
> Prod ID / Prod Name / Month / Qty
> 123 Book A Jan 20
> 123 Book A Feb 30
> 123 Book A Mar 40
> and so on & so forth.....
>
>
> AND if i have a similar Summary Sheet having the Product wise Value/Price in
> Sheet.2,
>
> how do i have the Database Table as
> Prod ID / Prod Name / Month / Value , where value = Sheet.1-Qty x
> Sheet.2-Value.
>
> Pls. help


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Fred]

Perhaps the transpose worksheet function (see Help) will work?

"sansk_23" wrote:

> If i have a Summary Sheet.1 like
>
> Prod ID / Product Name / Jan / Feb / Mar / Apr / May / June -------
> 123 Book A 20 30 40 50 60 70
> 234 Book B 10 20 40 60 30 50
>
> How do i get the Database Table as -
> Prod ID / Prod Name / Month / Qty
> 123 Book A Jan 20
> 123 Book A Feb 30
> 123 Book A Mar 40
> and so on & so forth.....
>
>
> AND if i have a similar Summary Sheet having the Product wise Value/Price in
> Sheet.2,
>
> how do i have the Database Table as
> Prod ID / Prod Name / Month / Value , where value = Sheet.1-Qty x
> Sheet.2-Value.
>
> Pls. help

[sansk_23]

you can try this :

http://j-walk.com/ss/excel/usertips/tip068.htm

For trying this in MS Access, first one has to design create a similar
database (collection of tables) & then copy the data into the relevant table.
Its a time consuming process. I am looking for some solution in MS EXCEL.

"thecuzin" wrote:

> That is basicaly the reverse of a pivot table. I would love to know. In MS
> Access you could use a union query but I am stumped in excel.
>
> "sansk_23" wrote:
>
> > If i have a Summary Sheet.1 like
> >
> > Prod ID / Product Name / Jan / Feb / Mar / Apr / May / June -------
> > 123 Book A 20 30 40 50 60 70
> > 234 Book B 10 20 40 60 30 50
> >
> > How do i get the Database Table as -
> > Prod ID / Prod Name / Month / Qty
> > 123 Book A Jan 20
> > 123 Book A Feb 30
> > 123 Book A Mar 40
> > and so on & so forth.....
> >
> >
> > AND if i have a similar Summary Sheet having the Product wise Value/Price in
> > Sheet.2,
> >
> > how do i have the Database Table as
> > Prod ID / Prod Name / Month / Value , where value = Sheet.1-Qty x
> > Sheet.2-Value.
> >
> > Pls. help