0

I have a code that creates a comma separated text (in a cell) of the 'column titles' for the 5 largest cell values within a row. However, for example if a row only has 3 cells with values above 0 and the rest are 0, the script still adds 2 of the cells with a 0 value as "column titles" to the comma separated text to complete the 5 items.

What formula modifications would carry out this same formula but ignore any cells that have a 0 value when creating the 'column titles' on the comma separated cell/text?

A little greater detail: In the example above where only 3 cells have values above 0, the formula should only show the 'column titles' for the top 3 largest cell values (that are > than 0) and not add any column titles to the comma separated text from cells that = 0. How would the formula be modified to accomplish this? Thanks

Google Sheets Code Example:

=join(" , ",sortn(transpose($BN$1:$CM$1),5,0,transpose($BN3:$CM3),false))

Reference:

  • $BN$1:$CM$1 (Are the Column Titles [text])
  • $BN3:$CM3 (Are the cells in row 3 that are transposed to show the (column titles) of the cells with the top 5 largest values.
1
  • Please edit your question and insert a table of sample data together with your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Jan 19, 2023 at 18:20

1 Answer 1

0

Use filter(), like this:

=join( 
  " , ", 
  sortn( 
    transpose(filter($BN$1:$CM$1, $BN3:$CM3 <> 0)),
    5, 0, 
    transpose(filter($BN3:$CM3, $BN3:$CM3 <> 0)), 
    false 
  ) 
)
1
  • Doubleunary you are amazing. Thank you so much, this works perfectly.
    – Matthew W
    Jan 19, 2023 at 18:23

Your Answer

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

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