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?

  • 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)
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    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

      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

        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.

Resources