M_8
12-02-2022, 06:02 AM
Hello,
I have very unusuall problem, yet still I think someone had to do it in the past...
I need a way to easly sort data in two way pivot table by specific rules.
Example: I have Customer Orders and Material (required for every C.O.). Value is amount of parts required from specific Material for every Order.
When I want to sort it with build-in Excel/Pivot (either Min;Max or Max;Min) sorting I've got something like that:
30361
while I wish to sort it according to set rules:
- 1st sort C.O. that uses only one Material and this Material is not used in other C.O. (smaller 1st rule?)
- next sort Material by the sequence for completed orders (preferably smaller orders completed faster)
- preferably do not "start" too much Materials
- spiliting Materials for two batches is possible
If I do it by hand i'm geting table like below (upside down "stairs"), but it's time consuming (up to 80 Materials and even 300 C.O. each day..)
30362
30363
Is it even possible in Excel/VBA? I'm looking for solutions but didn't found a single idea how I can make it more as automated process...
Any ideas where to start would be very helpfull...
I have very unusuall problem, yet still I think someone had to do it in the past...
I need a way to easly sort data in two way pivot table by specific rules.
Example: I have Customer Orders and Material (required for every C.O.). Value is amount of parts required from specific Material for every Order.
When I want to sort it with build-in Excel/Pivot (either Min;Max or Max;Min) sorting I've got something like that:
30361
while I wish to sort it according to set rules:
- 1st sort C.O. that uses only one Material and this Material is not used in other C.O. (smaller 1st rule?)
- next sort Material by the sequence for completed orders (preferably smaller orders completed faster)
- preferably do not "start" too much Materials
- spiliting Materials for two batches is possible
If I do it by hand i'm geting table like below (upside down "stairs"), but it's time consuming (up to 80 Materials and even 300 C.O. each day..)
30362
30363
Is it even possible in Excel/VBA? I'm looking for solutions but didn't found a single idea how I can make it more as automated process...
Any ideas where to start would be very helpfull...