Sorting two way pivot table with VBA?

[M_8]

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...

[Grade4.2]

Are you able to attach the workbook? I can have a look at it for you.

[M_8]

Hello,
I was able to prepare single day data (minimum required columns).

I did some looped sorting where i first count number of rows and columns in pivot sheet (pivot is presorted from high to low in both rows and columns), and then sort them in a way below
rows = number of rows counted from file
columns = number counted from file
r,c - counters for loops (rows/columns)

Starts with
c=2
r=rows

Effect isn't bad but far from perfect.


Do
ActiveSheet.Range(Cells(r, 2), Cells(rows, columns)).Sort Key1:=Cells(r, 2), _
Order1:=xlAscending, _ 'Descending?
Orientation:=xlSortRows
r = r - 1
Loop Until r = 4
Do
ActiveSheet.Range(Cells(5, c), Cells(rows, columns)).Sort Key1:=Cells(5, c), _
Order1:=xlAscending, _
Orientation:=xlSortColumns
c = c + 1
Loop Until c = columns + 1

[p45cal]

I'm really not sure what you want, so this is more in the way of an exploration. In the attached I've added 3 more columns to your DATA sheet, column D the count of different materials needed for each CO, and column E the quantity of material needed for that CO, then column F being the largest number of any single material for each CO (you might use this last for sorting).
I've used (some of) those values in the pivot table(s).
Getting close?