4

Thank you beforehand for your kind response.

Problem: I am trying to do a sheet to sort out the 3 top categories of a given range as shown by the attached screen grab.

enter image description here

I only managed to get the results by using the "large" command. "=large(range,n)". Using this I got the value but what I need is the column index/id rather than the numeric values.

Any ideas on how I would go about achieving this?

Thanks for your reply.

O'Neil Chung
  • 45
  • 3
  • Try this: `=MATCH(MAX(A2:E2),A2:E2,0)` – Muhammet Yunus Jul 22 '21 at 12:23
  • Thank you so much for the reply. I tried the formula and it returned the row number(?) and not the column id. Like if column D had the largest value, it should return the value "D" so that later I can put in an if statement to find the animal type. – O'Neil Chung Jul 22 '21 at 12:34
  • Look at [this](https://superuser.com/questions/316239/how-to-get-the-current-column-name-in-excel) please. This way is not recommended. You can use `=INDEX(MATCTH(...))` instead. Try this: `=INDEX(A1:E5;1; MATCH(MAX(A2:E2);A2:E2;0))` – Muhammet Yunus Jul 22 '21 at 12:52

3 Answers3

3

Try the following maybe:

enter image description here

Formula in G2:

=TRANSPOSE(QUERY(TRANSPOSE({A$1:E$1;A2:E2}),"Select Col1 Order by Col2 desc limit 3"))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Ooopp.... it got it wrong. It keeps giving me an error It keeps saying NO_COLUMN: Col2.... any idea whats wrong? – O'Neil Chung Jul 22 '21 at 13:28
  • @O'NeilChung, strange. It works flawlessly on my end. See the edited screenshot with the result when I past the formula in `G2` and drag it down the rows. It would only error out if you somehow changed the formula and the input for the query. – JvdV Jul 22 '21 at 13:31
  • 1
    `=TRANSPOSE(QUERY(TRANSPOSE({A$1:E$1**,**A2:E2}),"Select Col1 Order by Col2 desc limit 3"))` This is not a comma, but a semicolon. – Boris Baublys Jul 22 '21 at 13:35
  • 1
    @BorisBaublys, that's it. When I changed my settings from Dutch to US that's indeed what shows. The answer is ammended. – JvdV Jul 22 '21 at 13:39
  • YES!!! That was it Boris! Thank you so much to both JvdV and to Boris for the help. You all are amazing! Thank you so much! – O'Neil Chung Jul 22 '21 at 13:43
  • An additional question, can I put the formula into an ArrayFormula command? – O'Neil Chung Jul 22 '21 at 13:46
  • While I wrote a solution to get the letter of a column from its index, JvdV made for you Top3. I'll leave my solution here, maybe it will come in handy for someone: `=REGEXEXTRACT(ADDRESS(ROW(),MATCH(MAX(A2:E2,1),A2:E2,0),4),"\D+")` – Boris Baublys Jul 22 '21 at 13:48
  • I've learnt so much today and it's all thanks to you, Boris and JvdV. Both of you and Muhammet are amazing. Thank you so much. – O'Neil Chung Jul 22 '21 at 13:53
  • I tried this: =ArrayFormula(IF (ROW(A:A)=1,"",(TRANSPOSE(QUERY(TRANSPOSE({A$1:F$1;(A2:A):(F2:F)}),"Select Col1 Order by Col2 desc limit 3"))))) But ti keeps giving me the same result on every row – O'Neil Chung Jul 22 '21 at 14:06
  • 1
    @O'NeilChung. It's probably possible with a long wieldy formula that I don't know about. I'll leave that for someoneelse to figure out =) – JvdV Jul 22 '21 at 14:12
2

It's definitely possible in arrayformula.

Using vlookup, a sequence of numbers for the search key:

=arrayformula(sequence(max(if(A:E<>"",row(A:A),))-1,columns(A:E)))

enter image description here

...and a range of columns that are sorted by each row of numbers:

=arrayformula({
sequence((max(if(A:E<>"",row(A:A),))-1)*columns(A1:E1)),
sort({
array_constrain(int((row(A2:A)-2)/(columns(A:E)))+1,(max(if(A:E<>"",row(A:A),))-1)*columns(A1:E1),1),
array_constrain(iferror(split(flatten(if(A2:E<>"",A1:E1&char(9999),)&A2:E),char(9999)),),(max(if(A:E<>"",row(A:A),))-1)*columns(A1:E1),2)
},1,1,3,0)
})

enter image description here

The final formula for the solution, in cell G2:

=arrayformula(
array_constrain(
vlookup(
sequence(max(if(A:E<>"",row(A:A),))-1,columns(A:E)),
{sequence((max(if(A:E<>"",row(A:A),))-1)*columns(A1:E1)),sort({array_constrain(int((row(A2:A)-2)/(columns(A:E)))+1,(max(if(A:E<>"",row(A:A),))-1)*columns(A1:E1),1),array_constrain(iferror(split(flatten(if(A2:E<>"",A1:E1&char(9999),)&A2:E),char(9999)),),(max(if(A:E<>"",row(A:A),))-1)*columns(A1:E1),2)},1,1,3,0)}
,3,0),
max(if(A:E<>"",row(A:A),))-1,3)
)

enter image description here

It will work with blank cells in some of the rows.

The width of the source data is determined by:

=columns(A1:E1)

and the height of the source 'numbers' by:

=max(if(A:E<>"",row(A:A),))-1 (it needs to sit within an arrayformula).

Aresvik
  • 4,484
  • 1
  • 5
  • 18
  • Thank you so much for the help, @Aresvik. So detailed and expertly explained. Than kyou so much for the wonderful lesson. – O'Neil Chung Jul 23 '21 at 05:04
  • Updated solution to ignore blank cells. – Aresvik Jul 23 '21 at 07:06
  • Sorry to disturb you again, but after I have more than 20 rows of data, the formula stops working for all the cells with an error #REF! Function ARRAY_ROW parameter 2 has mismatched row size expected 414 actual 1. Any ideas what went wrong and is there a way to fix it? – O'Neil Chung Jul 31 '21 at 09:52
  • Can you share your sheet with the 20 rows of data so I can check it? – Aresvik Jul 31 '21 at 14:08
  • I took a copy of my sheet and had over 30 rows which seemed to work fine. I then flexed it to 1,000 and got the same error you mention. I then deleted all rows but 30 and I still get the error. I think it must be a Google limitation somehow. I've done further tests with a second sheet and it broke after 200 rows. Another test and this sheet works fine with over 500 rows: https://docs.google.com/spreadsheets/d/1vSwXa6h44UiRZ3zi_2IbB8Ap5-oDz-HTIbuVGxmQAZA/copy – Aresvik Jul 31 '21 at 14:45
1

you may use this superior formula:

=ARRAYFORMULA(ARRAY_CONSTRAIN(IFERROR(SPLIT(FLATTEN(SPLIT(QUERY(FILTER(
 QUERY(SPLIT(FLATTEN(IF(A2:E="",,ROW(A2:A)&"×"&A2:E&"×"&A1:E1)), "×"),
 "select Col3 where Col2 is not null order by Col1,Col2 desc"), COUNTIFS(
 QUERY(SPLIT(FLATTEN(IF(A2:E="",,ROW(A2:A)&"×"&A2:E&"×"&A1:E1)), "×"),
 "select Col1 where Col2 is not null order by Col1,Col2 desc"), 
 QUERY(SPLIT(FLATTEN(IF(A2:E="",,ROW(A2:A)&"×"&A2:E&"×"&A1:E1)), "×"),
 "select Col1 where Col2 is not null order by Col1,Col2 desc"), 
 SEQUENCE(COUNTA(A2:E), 1, ROW(A2)), "<="&SEQUENCE(COUNTA(A2:E), 1, ROW(A2)))<4)&
 FLATTEN(IFERROR(SEQUENCE(MAX((A2:E<>"")*ROW(A2:A)), 3)*
 {"a", "a", 0})),,9^9), 0)), " ")), 9^9, 3))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124