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.
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.
Try the following maybe:
Formula in G2:
=TRANSPOSE(QUERY(TRANSPOSE({A$1:E$1;A2:E2}),"Select Col1 Order by Col2 desc limit 3"))
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)))
...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)
})
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)
)
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).
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))
