Return top 5 occurrances

TB-User

I have a sheet to track customer requests (500+ rows), with a Salesperson column and customer column, the salesperson column is located farthest to the right of the sheet whereas the customer column is located on the 1st column. What I want to do is write a function that will reference a given Salesperson contact and be able to output the 1st most occurring customer, 2nd most occurring customer, etc. I want this to be a function so as entries get added throughout the year it will always grab the most occurring customer instead of manually looking through the sheet and writing a COUNTIFS formula looking for that manually entered customer.

Any help is appreciated, THANKS!

Richard Rymill SBP

Hi TB 

You can surface the stats you want in a series of reports using the filters and some well designed metrics in your sheets to pick up what you want to see. You can even view all this on a dashboard so everyone can see what is going on. 

Sounds like an interesting workflow? 

Hope that helps

Richardr@smarterbusinessprocesses.com 

Paul Newcome

You could add a helper column (we'll call it the Count column) and use something along the lines of

 

=COUNTIFS([Customer Column]:[Customer Column], [Customer Column]@row, [Sales Rep Column]:[Sales Rep Column], [Sales Rep Column]@row)

.

This would give you the number of times that particular customer is in the same row as that particular sales rep. I would then suggest another helper column (called [Row ID]) where you enter something along the lines of

 

=COUNTIFS([Customer Column]$1:[Customer Column]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

.

This will essentially replicate your row number into something that can be used in formulas.

 

From there you would use these two columns as reference in a LARGE statement.

 

=LARGE(COLLECT(Count:Count, [Sales Rep Column]:[Sales Rep Column], "John Doe"), 1)

 

will return the highest number in the Count column for John Doe.

 

=INDEX(COLLECT([Row ID]:[Row ID], Count:Count, number_returned_by_LARGE_function, [Sales Rep Column]:[Sales Rep Column], "John Doe"))

 

will return the first row number where that number is housed.

.

=INDEX([Customer Column]:[Customer Column], number_returned_by_previous_INDEX_function)

 

will give you the customer name.

.

If you are able to provide some more details as to the layout, I would be happy to tweak the above to reflect more accurately what your setup is. Will the metrics be on the same sheet or different sheet?

James McCombs

Hey TB,

 

I agree with what Paul said above - I think adding in a helper column is going to be the best option; I'd title it something like "Total Orders". I'd then use this formula in the Total Orders column:

=COUNTIF([Customer Name]:[Customer Name], [Customer Name]1)

This will count all the orders by any given customer, and return a numeric value.

I'd probably recommend doing the most-frequent-customers calculations in a separate sheet, where the Primary Column has your Sales Rep names, and the columns are 1st, 2nd, 3rd, etc.for customer frequency.

The following formula will give you the Customer Name that appears nth most frequently, where N is the ranking (1 - most frequent, 2 - 2nd most, etc). 

=INDEX(COLLECT([Customer Name]:[Customer Name], [Total Orders]:[Total Orders], LARGE([Total Completed]:[Total Completed], n)), 1)

If you're doing this in a separate sheet, you'll need to use the "reference sheet" option to tweak the formula above, so it should look something like this:

=INDEX(COLLECT({Customer Name}, {Salesperson}, $[Salesperson]1, {Total Orders}, LARGE({Total Orders}, n)), 1)

I think that should work to give you your rankings. You'll just need to make sure your sheet references are titled appropriately, and that "n" that is bolded and underlined above is replaced with a number to pull the customer with that rank position.