Reformatting a spreadsheet
Posted by Kate Fran on October 10, 2001 8:57 PM
I have a spreadsheet that contains columns for customer name, product name, Quantity, Price, Date of Sale. I want to reformat the spreadsheet to search on date of sale column and then produce a new spreadsheet with Total sales by customer by month. Format: Customer Name, Jan QTY, Jan Sale $, Feb Qty, Feb Sale $ ... to Dec QTY, Dec Sale$. please help.
Posted by Don C on October 11, 2001 5:43 AM
It may be the long way around the barn, but here is what I might do.
On the main worksheet create another column that combines the customer name and the month (using the order you gave this would be
=A2&" "&Mon(Date of Sale)
Then in another worksheet, I'd use SUMIF, matching to that new column.
Posted by Eric on October 11, 2001 7:08 AM
Pivot Table idea and a question
Using your data layout, with col titles in A1:E1, you could select your array (my dummy data went from A1:E47), then go Data|Pivot Table and Pivot Chart Report, click "next" twice, click "Layout", drag the "customer name" and "date of sale" buttons to the "row" area, and drag the "quantity" button to the "data" area, click "finish". On the resulting pivot table right click on the "date of sale" column and go Group and Outline|Group|Months. This should give you quantity sold broken down by month and customer.
One note though, if you want total sales, wouldn't it be more relevant if you added another column E2 that had =c2*d2 (price multiplied by quantity, remember to format as currency) and called it "total"? you could follow the pivot table routine listed above and just drag the "total" button in to data rather than the "quantity" button.
If the pivot table results aren't formatted as currency you can select them and format them just as you would normal cells.
Also make sure that the "Date of sale" column is actually in "date" format.
HTH
