If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Customer name when in TOP 5 growth
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 |
#2
|
|||
|
|||
Customer name when in TOP 5 growth
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 |
#3
|
|||
|
|||
Customer name when in TOP 5 growth
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 |
#4
|
|||
|
|||
Customer name when in TOP 5 growth
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 |
Thread Tools | |
Display Modes | |
|
|