I have a Google Sheet formula designed to pull the top 5 values out of a range of columns, display each of them separated by columns, calculate them as a percentage of a reference column, and append the name of the column each percentage is from. The formula looks like this:

=TEXTJOIN(", ", TRUE, ARRAYFORMULA(INDEX(C$1:AE$1, MATCH(LARGE(C2:AE2, SEQUENCE(5, 1)), C2:AE2, 0) + COLUMN(C2) - 3) & ": " & ROUND(LARGE(C2:AE2, SEQUENCE(5, 1)) / AH2 * 100, 2) & "%"))

It works correctly to pull the values, but it appends the name of "ColumnC" in front of every percentage, instead of the column title that the value was from.

E.g: the result returned should be something like:

"Apples: 20%, Oranges: 15%, Lemons: 12%, Bananas: 10%, Grapes: 7%"

but it returns:

"Apples: 20%, Apples: 15%, Apples: 12%, Apples: 10%, Apples: 7%"

Is there a way I can fix this so it pulls the appropriate column header name each % falls under instead?

Thanks!

Edit: sample data looks like this, sorry if I balls this up as first time trying:

I have a Google Sheet formula designed to pull the top 5 values out of a range of columns, display each of them separated by columns, calculate them as a percentage of a reference column, and append the name of the column each percentage is from. The formula looks like this:

=TEXTJOIN(", ", TRUE, ARRAYFORMULA(INDEX(C$1:AE$1, MATCH(LARGE(C2:AE2, SEQUENCE(5, 1)), C2:AE2, 0) + COLUMN(C2) - 3) & ": " & ROUND(LARGE(C2:AE2, SEQUENCE(5, 1)) / AH2 * 100, 2) & "%"))

It works correctly to pull the values, but it appends the name of "ColumnC" in front of every percentage, instead of the column title that the value was from.

E.g: the result returned should be something like:

"Apples: 20%, Oranges: 15%, Lemons: 12%, Bananas: 10%, Grapes: 7%"

but it returns:

"Apples: 20%, Apples: 15%, Apples: 12%, Apples: 10%, Apples: 7%"

Is there a way I can fix this so it pulls the appropriate column header name each % falls under instead?

Thanks!

Edit: sample data looks like this, sorry if I balls this up as first time trying:

• google-sheets
• google-sheets-formula

• 1 You shouldn't combine Exel tags with Google-sheets, although similar, there's a lot of subtle differences. Also, try to include sample data through text, Markdown Table generator can help you with this. – Excellor Commented Nov 20, 2024 at 8:21
• Post sample data to the post. – Harun24hr Commented Nov 20, 2024 at 8:27

2 Answers 2

Try the following QUERY() formula-

=TEXTJOIN(", ",1,QUERY(SORT(TRANSPOSE({C1:AG1;INDEX(C2:AG2/AH2)}),2,0),"limit 5 format Col2 '0.00%'"))

Edit: Adding updated formula using BYROW().

=BYROW(C2:AG,LAMBDA(r,IF(COUNTIF(r,">0")=0,,TEXTJOIN(", ",1,QUERY(TRANSPOSE({C1:AG1;INDEX(r/CHOOSEROWS(AH:AH,ROW(r)))}),"order by Col2 DESC limit 5 format Col2 '0.00%'")))))

You can try this:

=JOIN(", ",LET(x, ARRAY_CONSTRAIN(SORT(HSTACK(TOCOL(C1:AG1),TOCOL(C2:AG2)),2,FALSE),5,2), y,BYROW(CHOOSECOLS(x,2), LAMBDA(r, CONCATENATE(ROUND((r/AH2)*100,0),"%"))), BYROW(HSTACK(CHOOSECOLS(x,1),y), LAMBDA(t,JOIN(":",t)))))

Sample Output:

Please make sure that the range is going to be changed accordingly.

Update formula: For dynamic spilling formula you can use this.

=BYROW(C2:AG15, LAMBDA(zz, JOIN(", ",LET(x, ARRAY_CONSTRAIN(SORT(HSTACK(TOCOL(C1:AG1),TOCOL(zz)),2,FALSE),5,2), y,BYROW(CHOOSECOLS(x,2), LAMBDA(r, CONCATENATE(ROUND((r/AH2)*100,0),"%"))), BYROW(HSTACK(CHOOSECOLS(x,1),y), LAMBDA(t,JOIN(":",t))))))) 

Note: To change the range it is going to process, please change the C2:AG15 accordingly.

Here's another Formula:

=LET(x, ARRAY_CONSTRAIN(SORT(HSTACK(TOCOL($C$1:$AG$1),TOCOL(C2:AG2)),2,FALSE),5,2), y,BYROW(CHOOSECOLS(x,2), LAMBDA(r, CONCATENATE(ROUND((r/AH2)*100,0),"%"))), JOIN(", ",BYROW(HSTACK(CHOOSECOLS(x,1),y), LAMBDA(t,JOIN(": ",t)))))

I edited the formula so you can drag it down easily on your project. I made it this way for you to be able to apply this on your sheet it seems that there are a lot of things unknown with your current data set.

Sample Output:

Reference:

Join

Let

ByRow