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...
In the attached file, these are my formulas:
1. Q1, copied across to U1:
=INDEX($A10:$A14,
COLUMN()-16)
2. P2, copied down rows:
=IF(ROW()-1>COUNT($B$10:$N$10),"",
LOOKUP(2,1/($B$10:$N$10*(COUNTIF(P$1:P1,$B$9:$N$9)=0)=MAX(INDEX(
$B$10:$N$10*(COUNTIF(P$1:P1,$B$9:$N$9)=0),0))),
$B$9:$N$9))
3. Q2, copied down rows and across to Column U:
=IF($P2="","",
HLOOKUP($P2,$B$9:$N$14,
COLUMN()-15,0))