Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Pricelists from product list in Excel 2003
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Pricelists from product list in Excel 2003
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "berniean" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Pricelists from product list in Excel 2003
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 "Shane Devenshire" wrote: 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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "berniean" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generate Pricelist from Product list in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 Vista get Aero and product key box | Excel Discussion (Misc queries) | |||
Creating a Product List | Excel Discussion (Misc queries) | |||
Creating a Product DATABASE in ACCESS or EXCEL and then retrieving | Excel Discussion (Misc queries) | |||
Product list to match price list | Excel Discussion (Misc queries) |