Forum Discussion

KathyB1570's avatar
KathyB1570
Copper Contributor
Sep 30, 2024

Custom Sort for Weight Distribution

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?
excel
Formulas and Functions
JKPieterse's avatar
JKPieterse
Silver Contributor
Sep 30, 2024
Can you perhaps give us a short list of example numbers and how you expect them to be sorted?
KathyB1570's avatar
KathyB1570
Copper Contributor
Sep 30, 2024

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

 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 30, 2024

    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.

      • KathyB1570's avatar
        KathyB1570
        Copper Contributor
        Oct 02, 2024
        That's amazing, thank you! I'll try that today.

Resources