Forum Discussion

Dichotomy66's avatar
Dichotomy66
Brass Contributor
Apr 23, 2019

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...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 30, 2019

Dichotomy66 

I had a go at using my normal methods (pre-dynamic arrays) but with limited success.

Because I used COUNTIFS to rank the terms, I had to introduce a helper row as the criterion range.  The formula I used for the 'criteria' range was

= 100*(RA>50) + 10*(RN>RD) + (RN=RD)

[using a decimal place for each discrete sub-criterion]

 

The formula for ranking the terms by the criteria was

= 1 +

COUNTIFS(Criteria, ">"&Criteria) +

COUNTIFS(RD,">"&RD, Criteria, Criteria) +

COUNTIFS( k, "<"&k, Criteria, Criteria, RD, RD )

 

To place the results in rank order and transpose, I defined 'pointer' to be

= MATCH( TRANSPOSE(k), rank, 0)

 

That just leaves an array formula to output the result

= INDEX( Code, pointer )

 

I guess this doesn't look much like other people's solutions but it is still valid Excel and is about to get so much easier with modern dynamic arrays.

Resources