Creating Pricelists from product list in Excel 2003

[berniean]

Hello, I don't know if this belongs here or in the programming
section, so I'll start here. My company produces Excel pricelists for every
product we sell. The pricelists group the products by class, and include
prices for 3 different currencies in 4 different organizations.

The product list looks like this:
CLASS SKU PRODUCT UPC Org1 Org2 Org3 Org4
Class1 SKU1 Prod1 UPC1 P1 P2 P3 P4
Class1 SKU2 Prod2 UPC1 P1 P2 P3 P4
Class2 SKU3 Prod3 UPC3 P1 P2 P3 P4 etc.

Price list looks like this:
Class1
SKU1 Prod1 UPC1 P1 P2 P3 P4
SKU2 Prod2 UPC2 P1 P2 P3 P4
Class2
SKU3 Prod3 UPC3 P1 P2 P3 P4 etc.

Currently I have to set up the Class headers (which can change), then copy
and paste the rows for the products under the headers. If a product gets
added or dropped in the product list, I have manually add or delete the rows
in the pricelist. We have 10 pricelists, 5 with 10 tabs, 5 with 1 tab, for
the various currencies we deal with. This is a very manual, labor intensive
process, and prone to error.

I want to automate the process, but, unfortunately, MS Access is not a
possibility. My question: is there a way, using formulas or vb, to extract
the classes as headers, and the products as data in the format above. I'm
basically taking the left column of the product list and using it to create
group headers on the pricelist. I would like the pricelist to automatically
adjust to any changes in the product list.

A long post, I know, but any suggestions will be greatly appreciated!
Thanks, Bernie

 

[Shane Devenshire]

Hi,

Here's a quick start, the result are a wee tad different from yours but they
may meet your needs. If not just post the result from my steps back and ask
for help to get it to you layout.

1. Select the data and choose Data, PivotTable and PivotChart Report, Finish
2. Drag the Class, SKU, Product, UPC fields to the row area in that order.
3. Double click the Class title button and choose None under Subtotals
(repeat this step for SKU and Product.
4. Drag the Org1, Org2, Org3 and Org4 files to the Data area.
5. Drag the Data title button and drop it at the top of the column which
says Total (right on top of Total).
6. Select the entire pivot table and press Ctrl+C. Then choose Edit, Paste
Special, Values.
7. Delete anything you don't want like all the titles.

 

[berniean]

Shane,
Thank you for the response. However, two things. First, a PivotTable report
doesn't have the format my customers expect, and second I'm not certain what
the "Data title button" is. The data is under the Org1, Org2, etc. columns,
that is, the price of the product in that org.

I followed your sequence but only dragged one Org over to the Total column.
It copied every price on the product list as a column in the PivotTable. So,
that must not be what you meant by Data title.

Thanks again,
Bernie