0

Thank you to doubleunary for quickly solving my first of two questions. I am now able to create a single cell that properly lists all of the top 5 largest value column titles separated by commas; even when the top 2 values were the same.

Part 2 of 2 of the Question:

I am looking to utilize the same type of code that grabs the proper 1st, 2nd, 3rd, 4th, and 5th (Column titles) by top 5 largest values one-by-one (Rather than have them all separated by commas in a single cell.) Especially when the 1st and 2nd values are the same, like doubleunary fixed.

Simply I would like to return the column titles one by one and have them appear in their own cells with their value in parenthesis for each of the 5 largest values.

1st Largest Column Title and (Value) is placed in A5, 2nd largest is placed in B5, 3rd largest is placed in C5, 4th largest in D5, 5th largest in E5.

Example of Final Data requested

Background of issue: I am using a formula to determine the top 5 highest values in a row on google sheets. However, anytime the 1st and 2nd highest values are identical value, Google Sheets duplicates the same column title associated with the 1st highest value cell for the 2nd highest value. I assume because the 1st and second highest values are the same it just grabs the 1st highest value/column position twice.

0

1 Answer 1

0

Use sortn(), like this:

=sortn( 
  transpose(AM1:BL1), 
  5, 0, 
  transpose(AM3:BL3), 
  false 
)

To join those results into one text string, use textjoin(), like this:

=textjoin( 
  ", ", true, 
  sortn( 
    transpose(AM1:BL1), 
    5, 0, 
    transpose(AM3:BL3), 
    false 
  ) 
)

To get a horizontal table that lists the top titles with their values in parentheses, use transpose(), like this:

=transpose( 
  sortn( 
    transpose(AM1:BL1) & " (" & transpose(AM3:BL3) & ")", 
    5, 0, 
    transpose(AM3:BL3), 
    false 
  ) 
)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.