Custom Sort for Weight Distribution

[KathyB1570]

I'm trying to figure out a way to sort a list of pallets by weight, but the list needs to have the smallest values at the beginning and end with the highest weights in the middle.  Is this possible?

[#2]

Can you perhaps give us a short list of example numbers and how you expect them to be sorted?

[#3]

@JKPieterse  For example, if I have the following pallets to load onto a truck, I first calculate the weight.  (First row.)  Then I have that chart sorted to list them in ascending order, lowest to highest.  (Second row.)  Then I have to manually cut and paste every other cell and place them in descending order so that the heaviest pallets are in the center of the trailer and the lighter ones are in the front and back.  (Last row.)

[#4]

@KathyB1570 

This is strictly an Excel 365 formula.  The first step was to stack copies of the weights, one sorted ascending and the other descending.

= VSTACK(SORT(pallets,,1), SORT(pallets,,-1))

You would then select every alternate value down the list.  This can be achieved by using WRAPROWS and TAKE.

= TAKE(
    WRAPROWS(
      VSTACK(SORT(pallets), SORT(pallets,,-1)),
    2)
  ,,1)

[#5]

@KathyB1570 

Carrying on the same theme, because messy formulas like that are a distraction in your workbook, it would be possible to define your own function 'AlternatingSortλ' (say)

AlternatingSortλ
=LAMBDA(p,
    LET(
        stackedSort, VSTACK(SORT(p), SORT(p, , -1)),
        TAKE(WRAPROWS(stackedSort, 2), , 1)
    )
);

Workheet formula
= AlternatingSortλ(pallets)

A slight change to the function would allow it to output Pallet IDs as well as their weights.

[#6]

Just in case a 365 user comes across this now or in the future, here is the workbook.

[#7]

That's amazing, thank you! I'll try that today.