Fix Google Sheets formula so it will append the header title of each column it is pulling data from, instead of just the first c

admin管理员组

文章数量:1334887

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:

CODE	FruitBasket_Name	Apple	Banana	Peach	Pear	Grapefruit	Plum	Grape	Mango	Watermelon	Lemon	Lime	Mandarin	Jackfruit	Blueberry	Pineapple	Kiwi	Boysenberry	Fig	Lychee	Blood Orange	Papaya	Passionfruit	Cantaloupe	Serbian	Cranberry	Cherry	Clementine	Persimmon	Satsuma	Raspberry	Rockmelon	TotalFruit	Top5
10001	FruitBasket_1	12	3	0	0	0	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0	5	33	Apple: 36.36%, Apple: 30.3%, Apple: 24.24%, Apple: 9.09%, Apple: 0%
10002	FruitBasket_2	606	15	179	276	8	379	87	12	66	98	44	118	964	4	94	77	160	13	13	40	54	14	80	90	7	69	10	175	6	1313	151	4200

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:

CODE	FruitBasket_Name	Apple	Banana	Peach	Pear	Grapefruit	Plum	Grape	Mango	Watermelon	Lemon	Lime	Mandarin	Jackfruit	Blueberry	Pineapple	Kiwi	Boysenberry	Fig	Lychee	Blood Orange	Papaya	Passionfruit	Cantaloupe	Serbian	Cranberry	Cherry	Clementine	Persimmon	Satsuma	Raspberry	Rockmelon	TotalFruit	Top5
10001	FruitBasket_1	12	3	0	0	0	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0	5	33	Apple: 36.36%, Apple: 30.3%, Apple: 24.24%, Apple: 9.09%, Apple: 0%
10002	FruitBasket_2	606	15	179	276	8	379	87	12	66	98	44	118	964	4	94	77	160	13	13	40	54	14	80	90	7	69	10	175	6	1313	151	4200

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Nov 20, 2024 at 10:59 GeezerTD asked Nov 20, 2024 at 8:11 GeezerTDGeezerTD 1144 bronze badges 2

• 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

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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

本文标签：