Hi

I am trying to generate a summary report of the top 5 and bottom 5 growth
customers for each product range. The growth data and customer names are in
one sheet and the summary is another. I understand how to bring back the top
and bottom 5 numeric figures in each product typer by using the large and
small formula's however I also need to bring back the customer name that
relates to that figure.

Any help would be great. Oh! and I can't use pivot tables if at all possible.

Cin

Well, you would normally use an INDEX/MATCH combination to return the
name from your list where there is a corresponding match with the
growth value that you have identified with your LARGE and SMALL
functions.

The main problem with this is that MATCH will only find the first
match where you might have 2 or more names with the same growth
figure, so you have to devise a way of picking up those duplicates.

I can't give you specific formulae as you give no details of how your
data is laid out (and it's getting late here), but if you provide
further details I'm sure someone will pitch in while I'm having a
sleep.

Hope this helps.

Pete

On Nov 5, 12:50*am, Cinny wrote:
Hi

I am trying to generate a summary report of the top 5 and bottom 5 growth
customers for each product range. *The growth data and customer names are in
one sheet and the summary is another. I understand how to bring back the top
and bottom 5 numeric figures in each product typer by using the large and
small formula's however I also need to bring back the customer name that
relates to that figure. *

Any help would be great. *Oh! and I can't use pivot tables if at all possible.

Cin

Hi

Thanks Pete, have a good sleep.

Here is some more detail of what I am looking to do.

On the growth worksheet in Column A customer names are listed, then across
the rows, the growth for different product lines are provided in column B, C,
D for each customer as seen below.

Customer Name PET Bottles Growth CAPS Growth Liquid Growth
20056005 Energy 548,071 88,739 24,104
20014927 Coles 77,782 68,436 5,401

On the summary page you then have a column for each product, with the
product heading, then the actuals in total received, variance to budget,
growth etc. Then under this I would like to have the bottom and top 5 growth
customers for each product area. so in column A I would want the name of the
1st top growth customer brought for PET Bottles and then in Column B I would
want the value of the growth. as seen below... Thanks for any help.

PET Bottles CAPS Growth

Customers Customers
Actuals 775,004 Actuals 849,893
Var to Budget 362,005 Var to Budget 7,384

Growth Growth
Actual Growth 5.6% Actual Growth (3.3%)
Target Growth (7.3%) Branch Growth 10.0%

Top 5 Growth Customers Top 5 Growth Customers
20056005 Energy 548,071 20014927 Coles 68,436


Bottom 5 Growth Customers Bottom 5 Growth Customers



"Pete_UK" wrote:

Well, you would normally use an INDEX/MATCH combination to return the
name from your list where there is a corresponding match with the
growth value that you have identified with your LARGE and SMALL
functions.

The main problem with this is that MATCH will only find the first
match where you might have 2 or more names with the same growth
figure, so you have to devise a way of picking up those duplicates.

I can't give you specific formulae as you give no details of how your
data is laid out (and it's getting late here), but if you provide
further details I'm sure someone will pitch in while I'm having a
sleep.

Hope this helps.

Pete

On Nov 5, 12:50 am, Cinny wrote:
Hi

I am trying to generate a summary report of the top 5 and bottom 5 growth
customers for each product range. The growth data and customer names are in
one sheet and the summary is another. I understand how to bring back the top
and bottom 5 numeric figures in each product typer by using the large and
small formula's however I also need to bring back the customer name that
relates to that figure.

Any help would be great. Oh! and I can't use pivot tables if at all possible.

Cin

Hi,
You can use Rank() formula to get the ranks of all the customers. Then use
vlookup formula to get the data for the customers by putting rank in
"lookup_value" field of vlookup formula.

rank can be calculated as =RANK(B2,B2:B8) ...example

then at the place where u require top 5 values , use =vlookup(1,
......and other values.
--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast


"Cinny" wrote:

Hi

Thanks Pete, have a good sleep.

Here is some more detail of what I am looking to do.

On the growth worksheet in Column A customer names are listed, then across
the rows, the growth for different product lines are provided in column B, C,
D for each customer as seen below.

Customer Name PET Bottles Growth CAPS Growth Liquid Growth
20056005 Energy 548,071 88,739 24,104
20014927 Coles 77,782 68,436 5,401

On the summary page you then have a column for each product, with the
product heading, then the actuals in total received, variance to budget,
growth etc. Then under this I would like to have the bottom and top 5 growth
customers for each product area. so in column A I would want the name of the
1st top growth customer brought for PET Bottles and then in Column B I would
want the value of the growth. as seen below... Thanks for any help.

PET Bottles CAPS Growth

Customers Customers
Actuals 775,004 Actuals 849,893
Var to Budget 362,005 Var to Budget 7,384

Growth Growth
Actual Growth 5.6% Actual Growth (3.3%)
Target Growth (7.3%) Branch Growth 10.0%

Top 5 Growth Customers Top 5 Growth Customers
20056005 Energy 548,071 20014927 Coles 68,436


Bottom 5 Growth Customers Bottom 5 Growth Customers



"Pete_UK" wrote:

Well, you would normally use an INDEX/MATCH combination to return the
name from your list where there is a corresponding match with the
growth value that you have identified with your LARGE and SMALL
functions.

The main problem with this is that MATCH will only find the first
match where you might have 2 or more names with the same growth
figure, so you have to devise a way of picking up those duplicates.

I can't give you specific formulae as you give no details of how your
data is laid out (and it's getting late here), but if you provide
further details I'm sure someone will pitch in while I'm having a
sleep.

Hope this helps.

Pete

On Nov 5, 12:50 am, Cinny wrote:
Hi

I am trying to generate a summary report of the top 5 and bottom 5 growth
customers for each product range. The growth data and customer names are in
one sheet and the summary is another. I understand how to bring back the top
and bottom 5 numeric figures in each product typer by using the large and
small formula's however I also need to bring back the customer name that
relates to that figure.

Any help would be great. Oh! and I can't use pivot tables if at all possible.

Cin