I'm trying to take sales data by sku and be able to count the number of orders that occur within each qty break for each year as well as sum the sales in the same manner....can this be done with a macro? Or would you recommend another way of approaching this.
thanks for all the help.
Hi,
Normally for stuff like this it's better to create a database layout that can be used by a Pivot Table. This makes analysis and summarisation so much easier.
It's difficult to comment with certainty since I don't understand how the Row 1 labels on the Result sheet relate to your data.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Yeah sorry about that with the last sheet....I was just trying to show a basic layout but didn't add the final results in.
Basically for an item that sold in a particular year I was looking to count the number of orders and sum the sales at each break level. So for example if the 2nd break for an item is at qty 3 then break one would have all the orders and sales for that item where the qty in the order was 1 or 2.
Not really sure how to do it as a pivot table with all of the various breaks, about 200 break codes in total with each having different qty break sequence, and being able to sum at each cleanly.
Is there a separate area for pivot table help? Not sure how this would be done using one...
I'm not entirely sure what you mean by a break for an order and a break for a quantity, or indeed if the task is to summarise by period the quantity and sales why this is relevant.
I'm attaching a Pivot Table that summarise your data whatever order in which it happens to be listed.
Is this the sort of thing you're after. Don't get too hung up on the layout of the PT you can move fields around or add new ones in seconds to see a different analysis.
That's not what I'm looking for.
If you look at the first sheet PB it has break codes listed with their qty breaks. In the data each item has a break code. I want to count the orders and sum the sales by year by each individual break code.
For example all items with break code 121 at each year would have all the orders with qty of 1 counted at break one and all orders with qty of 2 or more, since there are no breaks after that, would have its orders counted at break 2. Some of the items have up to 7 breaks.
Posted this on another website...hopefully someone can help figure this out.
here's the link
http://www.mrexcel.com/forum/excel-q...ml#post4361102
Hi
You need to understand that whilst you are of course entirely familiar with your terminology being meat and drink to you, often it doesn't mean anything to us or if it does may mean something different to us. Therefore you need to guide us carefully through your data.
What is a break? What do you mean by a quantity break? what does B1, B2 B3 mean are these 'breaks'?
Similarly your data is limited so doesn't really allow us to see any pattern
Is Item AA121 something to do with break number 121? But if so where are break codes 169 & 201 in your Data sheet?
The easiest way to explain all this is to show us what data you start with and what the result looks like after whatever summarisation you want to happen has happened.
I fixed the example file. The final file would be 3 years of data but I made the ex only 2 to make it easier to look at.
Quantity breaks are a discount on the price based on what the quantity is for an item on each individual order. So for example if They order 1 then its at break 1 for the 3 examples. It moves to the next break when they order a quantity for that item that equals or goes about the next break on the chart.
did it make anymore sense with the new example file?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks