Hi I have a column A with sales people and B with Location.
Line items represent customers, so a sales person will appear many times in
the data set.

I would like column C to return the most frequently occuring location for a
particular sales person, and D to return the number of occurences of that
location.

E should return the second most frequently occuring location, and F the # of
occurences.

Any suggestions?

Thanks.
singh

This is a little challenging to do with formulas. Personally, I'd use a
pivot table to count the occurrences by sales person and location. You could
also flip it to see the sales people and count of salespeople for each
location, which might be an interesting analysis.

Alternatively, you could link your data into Access and use a Top 2 values
query to very quickly get your answer.

However, if you definitely want to get it done in Excel, it can be done. I
can accomplish it in the following way.

I assumed a customer number in column A, a Salesperson in column B, and a
Location in column C:

Customer SalesPerson Location
12 Dan FL

Column D can then be a concatenation of Salesperson and location. Assuming
the data starts in row 5, the column D formula would be: =B5&C5.

Customer SalesPerson Location PersonLocation
12 Dan FL DanFL


You can then use a sumif in column E (we'll call that column 'Frequency').
Cell E5 would look like this: =COUNTIF($D$5:$D$5000,"="&B5&C5)

Customer SalesPerson Location PersonLocation Frequency
12 Dan FL DanFL
4

In Column F, then (I'll call it 'TOP2'),we can evaluate the frequency in
column E to determine if we have a Top or Second value. Cell F5 would look
like this: =IF(B5<B4,"TOP",IF(F4="TOP","Second",""))

Customer SalesPerson Location PersonLocation Frequency Top
12 Dan FL DanFL
4 TOP

Finally, in column G, we can create another concatenation of the Person and
Rank. Cell G5 contains the formula: =B5&F5.

....SalesPerson Location PersonLocation Frequency Top PersonRank
.... Dan FL DanFL 4
TOP DanTOP

Now, things look a little funny, but if you sort by Salesperson and
Frequency (descending), things will start to look much better. The
PersonRank (column G) still looks a bit funny but now we can use it combined
with some MATCH and OFFSET functions to do the work you need to do.

Somewhere removed from your table, (I chose to start in I5), make a list of
your salespeople going from range I5 to I?. Assume salesperson Jack is in
I5. Next to him in J5, you can place the formula
=OFFSET($C$4,MATCH($I5&"TOP",$G$5:$G$5000,0),0)

This will give you the TOP location for good old Jack by finding the first
instance of JackTOP in column G. Once it matches JackTOP, it will offset
from C4 that number of rows and return the location. Assuming (hopefully!)
we've got all of our ranges laid out the same way (that is, with the Location
header in C4 and the first Location record in C5. If they're different,
you'll have to change the offset cell, which I have as $C$4)

From there, we can do more of the same to get the TOP location count in K5
with the formula =OFFSET($E$4,MATCH($I5&"TOP",$G$5:$G$5000,0),0)

And the Second location in L5 with the formula
=OFFSET($C$4,MATCH($I5&"Second",$G$5:$G$5000,0),0)

And (finally!) the Second location count with the formula
=OFFSET($E$4,MATCH($I5&"Second",$G$5:$G$5000,0),0) .

If you copy all of the formulas in J5-M5 down as far as you need to cover
all of your salespeople, you should be in business. To neaten things up, you
could also hide columns D:G. (WHEW!)

Again, it's a little tricky to do this way and I don't know if there's an
easier method other than doing a PivotTable or going into Access.

I hope this helps you solve your problem!

thanks. using concatenate to create the unique person/location indicators
was a clever solution. in the end, i just dumped the data into access - it
was surely a simpler solution (though, i try to avoid opening access at the
office as i dont want anyone to see i can actually use it).

"GSnyder" wrote:

This is a little challenging to do with formulas. Personally, I'd use a
pivot table to count the occurrences by sales person and location. You could
also flip it to see the sales people and count of salespeople for each
location, which might be an interesting analysis.

Alternatively, you could link your data into Access and use a Top 2 values
query to very quickly get your answer.

However, if you definitely want to get it done in Excel, it can be done. I
can accomplish it in the following way.

I assumed a customer number in column A, a Salesperson in column B, and a
Location in column C:

Customer SalesPerson Location
12 Dan FL

Column D can then be a concatenation of Salesperson and location. Assuming
the data starts in row 5, the column D formula would be: =B5&C5.

Customer SalesPerson Location PersonLocation
12 Dan FL DanFL


You can then use a sumif in column E (we'll call that column 'Frequency').
Cell E5 would look like this: =COUNTIF($D$5:$D$5000,"="&B5&C5)

Customer SalesPerson Location PersonLocation Frequency
12 Dan FL DanFL
4

In Column F, then (I'll call it 'TOP2'),we can evaluate the frequency in
column E to determine if we have a Top or Second value. Cell F5 would look
like this: =IF(B5<B4,"TOP",IF(F4="TOP","Second",""))

Customer SalesPerson Location PersonLocation Frequency Top
12 Dan FL DanFL
4 TOP

Finally, in column G, we can create another concatenation of the Person and
Rank. Cell G5 contains the formula: =B5&F5.

...SalesPerson Location PersonLocation Frequency Top PersonRank
... Dan FL DanFL 4
TOP DanTOP

Now, things look a little funny, but if you sort by Salesperson and
Frequency (descending), things will start to look much better. The
PersonRank (column G) still looks a bit funny but now we can use it combined
with some MATCH and OFFSET functions to do the work you need to do.

Somewhere removed from your table, (I chose to start in I5), make a list of
your salespeople going from range I5 to I?. Assume salesperson Jack is in
I5. Next to him in J5, you can place the formula
=OFFSET($C$4,MATCH($I5&"TOP",$G$5:$G$5000,0),0)

This will give you the TOP location for good old Jack by finding the first
instance of JackTOP in column G. Once it matches JackTOP, it will offset
from C4 that number of rows and return the location. Assuming (hopefully!)
we've got all of our ranges laid out the same way (that is, with the Location
header in C4 and the first Location record in C5. If they're different,
you'll have to change the offset cell, which I have as $C$4)

From there, we can do more of the same to get the TOP location count in K5
with the formula =OFFSET($E$4,MATCH($I5&"TOP",$G$5:$G$5000,0),0)

And the Second location in L5 with the formula
=OFFSET($C$4,MATCH($I5&"Second",$G$5:$G$5000,0),0)

And (finally!) the Second location count with the formula
=OFFSET($E$4,MATCH($I5&"Second",$G$5:$G$5000,0),0) .

If you copy all of the formulas in J5-M5 down as far as you need to cover
all of your salespeople, you should be in business. To neaten things up, you
could also hide columns D:G. (WHEW!)

Again, it's a little tricky to do this way and I don't know if there's an
easier method other than doing a PivotTable or going into Access.

I hope this helps you solve your problem!

My pleasure. It's true, the Access and Excel folks tend to work in different
camps. It seems that a lot of the more tricky problems on this board are
because folks are trying to get Excel to behave like Access. I guess it's
all in what you're most comfortable with.

Have a great day!