Top x per criteria (Function)

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

Lets say I have 50 sheets, one for each state. I also have a sheet
that has a years worth of data.

So the data sheet has something like this (simplified)

State Month Amount User
RI Jan 5100 1234
RI Jan 5101 1235
RI Jan 5001 1244
RI Feb 5100 1234
RI Feb 5111 4343
RI Feb 5122 2343
RI Feb 5122 2343
RI Feb 4900 3234
RI Mar 5100 2344
TX Jan 5100 2345
TX Feb 8999 0989
Etc


I want to find the top 3 Users where
1. The state equals a cell
2. The month equals a cell.
3. The amount is over a certain amount ( can be hardcoded as 5000)

So each state sheet looks something like this…

RI
Feb

User Amount


And I need it to return the top three amounts (and the User) per
state. I have been using Match and Large to try to pull them, but I'm
not getting anything to quite work. Getting close, but not quite.

Having ties is not an issue for what I need.


Thanks a ton in advance for any assistance.
 
Hi
if your report is on a separate sheet with the 3 conditions in A1:A3
enter the following formulas as array formulas (CTRL+SHIFT+ENTER) in

A4:
=INDEX(sheet1!$D$1:$D$12,MIN(IF((sheet1!$A$1:$A$12=$A$1)*(sheet1!$B$1:$
B$12=$A$2)*(sheet1!$C$1:$C$12=$B4),ROW(sheet1!$C$1:$C$12))))

B4:
=LARGE(IF((sheet1!$A$1:$A$12=$A$1)*(sheet1!$B$1:$B$12=$A$2)*(sheet1!$C$
1:$C$12>=$A$3),sheet1!$C$1:$C$12),ROW(1:1))

copy both formulas down
 
Ok, I got the Amount to work, having issues with the user name that
goes along with that value.

I copied the test I'm working with into a single sheet, and here is
what I get.

State Month Amount User RI
RI Jan 5100 a Feb
RI Jan 5101 b 5000
RI Jan 5001 c
RI Feb 5100 d
RI Feb 5111 1 d 5122
RI Feb 5122 2 d 5122
RI Feb 5122 3 d 5111
RI Feb 4900 4 User 5100
RI Mar 5100 5 User #NUM!
TX Jan 5100 6
TX Feb 8999 7


I expect the #num, and I'll worry about that after I get it all
working.

Here is my formula chunk. (I swapped $C$1:$C$12=$B4 to $C$1:$C$12>=$B4
as it looked like an error)


=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E3),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(1:1))

=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E4),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(2:2))

=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E5),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(3:3))

=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E6),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(4:4))

=INDEX($D$1:$D$12,MIN(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E7),ROW($C$1:$C$12))))
=LARGE(IF(($A$1:$A$12=$E$1)*($B$1:$B$12=$E$2)*($C$1:$C$12>=$E$3),$C$1:$C$12),ROW(5:5))
 
Back
Top