Forum Discussion
Sorting col large to small with duplicates and putting into a vertical table
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.
SO here is the solution I came up with in the end
IN the Data set I added an infintessimal
ex. =(COUNTIF(TYPERN,BG$10)/BG$11)+(1/(COLUMN()*10^5))
Then I created 4 conditional helper columns to sort and return column numbers
Helper 1 The Best
=IFERROR(MATCH(LARGE(IF(($BC$13:$BL$13>=0.5),$BC$13:$BL$13,"glo"),ROWS($T$2:$T2)),$BC$13:$BL$13,0),"")
Helper 2 The good
=IFERROR(MATCH(LARGE(IFS(($BC$13:$BL$13<0.5)*($BC$13:$BL$13>$BC$14:$BL$14)*($BC$13:$BL$13>$BC$15:$BL$15),$BC$14:$BL$14,($BC$14:$BL$14>=$BC$13:$BL$13)*($BC$14:$BL$14>$BC$15:$BL$15),$BC$14:$BL$14,($BC$14:$BL$14=$BC$13:$BL$13=$BC$15:$BL$15),$BC$14:$BL$14,TRUE,""),ROWS($U$2:$U2)),$BC$14:$BL$14,0),"")
Helper 3 the not so good
=IFERROR(MATCH(LARGE(IF(($BC$14:$BL$14=$BC$15:$BL$15)*($BC$14:$BL$14>$BC$13:$BL$13),$BC$14:$BL$14),ROWS($V$2:$V2)),$BC$14:$BL$14,0),"")
Helper 4 I ranked them with small so the worst appear at the bottom of the list
=MATCH(SMALL($BC$15:$BL$15,ROWS($W$2:$W2)),$BC$15:$BL$15,0)
SO the first 3 columns only have a few to zero values
Then I used this formula in my results column
=IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS(ROW(),20)))),(IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS((ROW()-(COUNT($T$2:$T$11))),21)))),(IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS(((ROW())-(COUNT($T$2:$T$11))-(COUNT($U$2:$U$11))),22)))),(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS(ROW(),23))))))))))
This returned the values based on the column number in the helpers and ranked the results from best to worst
- I guess you have not yet looked into the formulas I suggested in the file I attached earlier. Please do and inform me of your thoughts thereon.
TwifooI looked but your results didn't sort the subset results. And I couldnt see a simple way to modify this... The primary end goal is to have the LLL-HHH Values sorted from best to worst based on the criteria. I don't need or want any of the other data from the originating table transposed also. IN your results the one that was ^*% RA ended up down a ways instead of at the top for ex
I switched the values in row 12 to just numbers
RA 0.00 0.00 0.00 16.67 50.00 60.00 0.00 36.36 66.67 28.57 0.00 0.00 0.00 Now when I run the helper column the zeroes give big numbers that lead to blanks in the results column
696 23 22 385 597 691 24 20 19 18 MHH MMH HHH LHH LMH LMM There may be no real way to do this but RA stands for advantage RN neutral and RD disadvantage
If I could somehow have the results column sort row9 such that first from large to small was the ones where RA was over 50 then the ones where RN was greater than RD followed by RN=RD and lastly RD sorted so the result would in this case be
MHH LHH LMH LMM HHH MMH LLH MMM LLL LLM
Answered from mobile previous time, open the computer and didn't catch how row 12 (RA) affects the Total (row 10) and what they shall be. Anyway, I reworked the formula to avoid helper column as
=IFERROR( INDEX($B$9:$N$9,1, AGGREGATE(14,6, 1/($B$10:$N$10=AGGREGATE(14,6,$B$10:$N$10,ROW()-ROW($P$1)))* COLUMN($B$10:$N$10), SUMPRODUCT(--($B$10:$N$10=AGGREGATE(14,6,$B$10:$N$10,ROW()-1))))-COLUMN($A$10)), "")and now it shall not depend on which numbers are in Total row.
If again something is wrong please attach sample file there the issue is, from the text it's bit hard to understand without the knowledge what is behind your figures.
SergeiBaklanThanks that will work and luckily I have a place to hide the helper column close by
Dichotomy66 , you are welcome
