Forum Discussion
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?
- PeterBartholomew1Silver Contributor
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)
- JKPieterseSilver ContributorCan you perhaps give us a short list of example numbers and how you expect them to be sorted?
- KathyB1570Copper 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.)
- PeterBartholomew1Silver Contributor
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.