Multiple Columns into a single set of Columns in Excel

[hammer02]

I have a planning tool that was written in Excel. It has planned production by day for a given product. The products are in column A and the various days are in column B, C, D, ... Not every product is produced each day. I need to build an output file that lists which products are to be produced by day in a single set of columns (column A is the product, ColumnB is the date and column C is the amount. See below.

Planning Sheet:
ColA ColB ColC ColD
Product Day1 Day2 Day3
ProdA 10 5
ProdB 4 3
ProdC 2 6 8

What I need to produce is
ColA ColB ColC
Product Date Qty
ProdA Day1 10
ProdA Day3 5
ProdB Day1 4
ProdB Day2 3
ProdC Day1 2
ProdC Day2 6
ProdC Day3 8

What type of code or forumlas could I use. I'm not a very profficient VB coder. I was thinking of copying and pasting the data and then sorting it appropriately. What better suggestions do you have?

Thanks,

 

[Fenrirshowl]

Quickest way would be to do a pivot table.

Highlight the area including titles.
Data menu / Pivot table and pivotchart report
Click Next
Click Next
Select a worksheet for the report or the cell if you want it on the current worksheet
Click Layout
Drag "Product" from the right to the "Row" area
The drag "Day1", "Day2", "Day3" in turn to the data area.
You may have Sum of Day... for some and Count of Day... for others - don't worry.
Click OK
Click Finish


On the pivot table right-click each of the "Count of Day..." in turn, select Field settings, Summarize by Sum, then click ok.

That should be it.

You get a few entries where the Sum of a Day may be blank for certain products, but it is the quick way to achieve what you are after.

Hope this helps

Fen

 

[SkipVought]

faq68-5287.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}