I have a Google Sheet having 4 columns as
**A** **B** **ID** **Cost**
France 101 1 2,038.67
France 101 2 1,067.87
France 101 3 884.08
France 101 4 872.92
France 101 5 842.53
France 101 6 717.75
France 101 7 409.96
France 101 8 374.00
France 101 9 280.97
France 101 10 232.74
France 101 11 217.16
France 101 12 5.09
India 102 13 52,113.35
India 102 14 34,213.21
India 102 15 24,332.05
India 102 16 23,824.82
India 102 17 21,239.98
India 102 18 18,528.92
India 102 19 12,207.84
India 102 20 11,992.45
India 102 21 10,705.70
India 102 22 6,799.04
India 102 23 6,625.35
India 102 24 6,495.47
India 102 25 6,410.21
Now, I am looking for a formula to get top 5 values from Col 4 (cost) segmented by Col A
For ex, the final output should be like:
France 101 1 2,038.67
France 101 2 1,067.87
France 101 3 884.08
France 101 4 872.92
France 101 5 842.53
India 102 13 52,113.35
India 102 14 34,213.21
India 102 15 24,332.05
India 102 16 23,824.82
India 102 17 21,239.98
I belive this can be done by using Query and Limit formula, But i am unable to use LIMIT for segmenting on criteria basis.
Here's one approach (not query-based)
:
=reduce(A1:D1,unique(tocol(A2:A,1)),lambda(a,c,vstack(a,array_constrain(filter(A:D,A:A=c),5,4))))
array_constrain
is used here to set the limit as 5 rows * 4 columns
which you may change accordingly