Custom function to find top sellers

[nj]

OK, I give. I've worked on this off and on for days, I've hunted and
hunted for other posts that might be close enough to get me there, but
no luck. Is anyone up for helping with this?

I'm trying to set up a custom function to return all the members of a
team who made the top sales number. Here's some sample data:

Team Color Sales
Team A Red 87
Team A Blue 87
Team A White 29
Team A Black 28
Team B Yellow 83
Team B Green 4
Team B Teal 2
Team B Silver 1
Team C Tan 121
Team C Gray 119
Team C Brown 4
Team C Purple 2
Team D Aqua 94
Team D Gold 94
Team D Pink 29
Team D Orange 1

Now, I found and have used the max(if()) function to find that top
sale number per team,
=MAX(IF((Team=F$20),Sales,0)) -- array function, so entered with Ctrl
+Shft+Enter
(Which reminds me, I've used sumproduct for all the other
calculations because we find people break array functions done this
way, but DANGED if I can figure out how to work Max with Sumproduct.
Grrr.)

Oh, yes, forgot -- I've set up the dynamic named ranges as well, so
Team =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Sales =OFFSET(Sheet1!$A$2,0,2,COUNTA(Sheet1!$A:$A)-1,1)

So the Max results look like this:

Team A Team B Team C Team D
Max Sales 87 83 121 94

Now, if there could only be one top seller, I would have my solution
because I discovered Ctrl+Shft+Enter allow Match to work with arrays,
and I have the formula
=INDEX($A$2:$C$17,MATCH(F20&F21,$A$2:$A$17&$C$2:$C$17,0),2) -- array
function, so entered with Ctrl+Shft+Enter

so now it looks like this:

Team A Team B Team C Team D
Max Sales 87 83 121 94
Top Seller(s) Red Yellow Tan Aqua

But what I *really* need is:

Team A Team B Team C Team D
Max Sales 87 83 121 94
Top Seller(s) Red, Blue Yellow Tan Aqua, Gold

Is anyone up for messing with that?

I'd be very grateful if anyone were so inclined.

NJ

 

[Bob Phillips]

You could use this array formula

=IF(ISERROR(SMALL(IF((Team=F$20)*(Sales=F$21),ROW(Sales)-MIN(ROW(Sales))+1,""),ROW(A1))),"",INDEX(Colour,SMALL(IF((Team=F$20)*(Sales=F$21),ROW(Sales)-MIN(ROW(Sales))+1,""),ROW(A1))))

copy it down, it gives the values in separate rows

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[nj]

You could use this array formula

=IF(ISERROR(SMALL(IF((Team=F$20)*(Sales=F$21),ROW(Sales)-MIN(ROW(Sales))+1,­""),ROW(A1))),"",INDEX(Colour,SMALL(IF((Team=F$20)*(Sales=F$21),ROW(Sales)-­MIN(ROW(Sales))+1,""),ROW(A1))))

copy it down, it gives the values in separate rows

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)




















- Show quoted text -

Very interesting, but the real context is a summary report per site,
by team, where each col is a team and each row is another statistic
type. There are about 50 rows, and I would introduce a different
problem if the number of rows for a statistic varies.