Top N list with "all others"

[Ron H]

Greetings all, I wish to evaluate a list of customers.
I have 500 orders from approximately 30 unique customers.
I wish to create a list such as "Top Ten" and then place the remainin
20 or so in a row called "All others" and have their count noted.

In my Pivot Table I have a row field called "customers" and the dat
field is "Count of customers"

For those of you that are "Intuit Quicken" users you might know what
mean. When you graph expenses, the top 10 categories are shown wit
the last category being called "All other" with this one being a su
total of all the categories not in the top 9.

Example:
Customer A 10 orders
Customer B 7 orders
Customer C 5 orders
Customer D 2 orders
Customer E 1 order
Customer F 1 order
Customer G 1 order

I want to be able to generate a report showing
Customer A 10 orders
Customer B 7 orders
Customer C 5 orders
Customer D 2 orders
All others 3 orders

Thanks to al

 

[mk]

Greetings all, I wish to evaluate a list of customers.

I have 500 orders from approximately 30 unique customers.
I wish to create a list such as "Top Ten" and then place the remaining
20 or so in a row called "All others" and have their count noted.

In my Pivot Table I have a row field called "customers" and the data
field is "Count of customers"

For those of you that are "Intuit Quicken" users you might know what I
mean. When you graph expenses, the top 10 categories are shown with
the last category being called "All other" with this one being a sum
total of all the categories not in the top 9.

Example:
Customer A 10 orders
Customer B 7 orders
Customer C 5 orders
Customer D 2 orders
Customer E 1 order
Customer F 1 order
Customer G 1 order

I want to be able to generate a report showing
Customer A 10 orders
Customer B 7 orders
Customer C 5 orders
Customer D 2 orders
All others 3 orders

Maybe you have to add a new column that will show a clients name if it's
number of contracts is more than 1
and will show "all other" when number of contracts = 1, use this formula:

=IF(COUNTIF(customers;A2)>1;A2;"all other")

where 'customers' is a range with customer names and A2 contains a customer
name, then copy the formula down.

Marcin

 

[Ron H]

That forumula didn't seem to work.
Tried it with both a named range (customers) and manually entering th
range (e.g. L2:L500)




Originally posted by mk [/i]
*> Greetings all, I wish to evaluate a list of customers.

I have 500 orders from approximately 30 unique customers.
I wish to create a list such as "Top Ten" and then place th remaining
20 or so in a row called "All others" and have their count noted.

In my Pivot Table I have a row field called "customers" and the data
field is "Count of customers"

For those of you that are "Intuit Quicken" users you might know wha I
mean. When you graph expenses, the top 10 categories are shown with
the last category being called "All other" with this one being a sum
total of all the categories not in the top 9.

Example:
Customer A 10 orders
Customer B 7 orders
Customer C 5 orders
Customer D 2 orders
Customer E 1 order
Customer F 1 order
Customer G 1 order

I want to be able to generate a report showing
Customer A 10 orders
Customer B 7 orders
Customer C 5 orders
Customer D 2 orders
All others 3 orders

Maybe you have to add a new column that will show a clients name i
it's
number of contracts is more than 1
and will show "all other" when number of contracts = 1, use thi
formula:

=IF(COUNTIF(customers;A2)>1;A2;"all other")

where 'customers' is a range with customer names and A2 contains
customer
name, then copy the formula down.

Marcin

 

[Ron H]

I have an ugly method of doing this:

I have a pivot table with one column showing the individual names o
the customers and adjacent to it another column showing the count fo
the number of orders each customer placed.

I can copy my complete pivot table using paste special "as values"
elsewhere on the worksheet.

Then go back to the original worksheet and limit my pivot table to onl
show Top "N" (let's say top 5) ordering customers.

At the bottom of the column in my newly adjusted pivot table I put
"All others" which equals "=(Grand total of all customers as summe
from the copied pivot table)-Sum of the Top "N" in my current pivo
table.

I was just hoping there was a more elegant way. May require VBA whic
I have never done.

*That forumula didn't seem to work.
Tried it with both a named range (customers) and manually enterin
the range (e.g. L2:L500).
ALso, I am not sure that's quite what I had intended.
In a way I suppose I want to filter out the bottom customers; the
may show up more than once so I shouldn't use "1" as my criteria i
the COUNTIF statement.
I want to have the top ten, top five (or any number I pick) and the
have all others summed up.

Regards,
Ron




Originally posted by Ron H
> Greetings all, I wish to evaluate a list of customers.

I have 500 orders from approximately 30 unique customers.
I wish to create a list such as "Top Ten" and then place th remaining
20 or so in a row called "All others" and have their count noted.

In my Pivot Table I have a row field called "customers" and th data
field is "Count of customers"

For those of you that are "Intuit Quicken" users you might kno what I
mean. When you graph expenses, the top 10 categories are show with
the last category being called "All other" with this one being sum
total of all the categories not in the top 9.

Example:
Customer A 10 orders
Customer B 7 orders
Customer C 5 orders
Customer D 2 orders
Customer E 1 order
Customer F 1 order
Customer G 1 order

I want to be able to generate a report showing
Customer A 10 orders
Customer B 7 orders
Customer C 5 orders
Customer D 2 orders
All others 3 orders

Maybe you have to add a new column that will show a clients name i
it's
number of contracts is more than 1
and will show "all other" when number of contracts = 1, use thi
formula:

=IF(COUNTIF(customers;A2)>1;A2;"all other")

where 'customers' is a range with customer names and A2 contains
customer
name, then copy the formula down.

Marcin * [/B