# How to transpose the 5 highest row values only from cells whose value doesn't equal 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

=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.
• 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. Commented Jan 19, 2023 at 18:20

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
)
)
``````
• Doubleunary you are amazing. Thank you so much, this works perfectly. Commented Jan 19, 2023 at 18:23