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.

Improve this question
1

1 Answer 1

Reset to default
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 
  ) 
)
Improve this answer
4
  • Thank you I was able to modify your code so that all of the 5 outputs appear on a single line separated by commas with =join(",",sortn(transpose(AM1:BL1),5,0,transpose(AM3:BL3),false)) Could you guide me on how to directly grab the correct 2nd value in these instances where the 2 highest values are the same. Where I can have the 1st highest value go to cell A5, 2nd highest to B5, 3rd highest to C5 etc for all 5 largest values? Currently I'm using this to grab values from 5 largest in row - but it generates the original error =INDEX($AM3:$BL3,MATCH(LARGE($AM3:$BL3,1),$AM3:$BL3,0))
    – Matthew W
    Jan 18 at 21:20
  • I currently run this code for the top 5 values/array positions, although if 2 values are the same it runs into the error I mentioned originally in this post. =INDEX($AM3:$BL3,MATCH(LARGE($AM3:$BL3,2),$AM3:$BL3,0)) =INDEX($AM3:$BL3,MATCH(LARGE($AM3:$BL3,3),$AM3:$BL3,0)) =INDEX($AM3:$BL3,MATCH(LARGE($AM3:$BL3,4),$AM3:$BL3,0)) =INDEX($AM3:$BL3,MATCH(LARGE($AM3:$BL3,5),$AM3:$BL3,0))
    – Matthew W
    Jan 18 at 21:22
  • Hi Doubleunary, This is incredibly helpful thank you. To clarify I was looking for 2 types of code. 1) Which is solved to create a single cell that lists all of the top 5 column titles together separated by commas. 2) Is to grab each of the values of those 5 largest values in cells within a row individually. So I can take the 1st largest and put it in A5, 2nd largest and put it in B5 etc for all 5 top values (not column titles).
    – Matthew W
    Jan 18 at 21:24
  • Please ask only one question per post. Post a new question.
    – doubleunary
    Jan 19 at 18:09

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.