Forum Discussion
Sorting col large to small with duplicates and putting into a vertical table
I am trying to sort the horizonal values in the first row based upon the values in the second row and have the result be similar to the vertical table to the right I keep getting #num or it only ret...
You may first create helper column to find the positions with with the large to small in totals (in P2)
=IFERROR((AGGREGATE(14,6,$B$10:$N$10*1000+COLUMN($B$9:$N$9),ROW()-ROW($P$1))/1000-INT(AGGREGATE(14,6,$B$10:$N$10*1000+COLUMN($B$9:$N$9),ROW()-ROW($P$1))/1000))*1000,"")
based on that position you may select values from other rows like
=IFERROR(INDEX($B$9:$N$9,0,$P2-COLUMN($A$9)),"")
Drag the all down till first empty cells appear.
SergeiBaklanThanks that will work and luckily I have a place to hide the helper column close by
Dichotomy66 , you are welcome