Feb 12 2019 04:59 AM
I have one column (A) with nameIDs and one column with sales (B). Most of the nameIDs are repeating and there are many nameIDs.
1. Is there a function that can find the total highest sales (F4), the second highest and so on ?
2. Is there a function that then can find the nameID of the the total highest sales (E4), the second highest and so on?
3. or an efficient way to do this in excel?
I have attached an example.
Many thanks!
Feb 12 2019 09:16 AM
You may pivot your table and filer it on top 6, please see attached.
Feb 13 2019 12:32 AM
Many thanks!
I was hoping to avoid pivot as it needs to refresh every time my data changes.
Will it be possible to do via visual basic?
Still, many thanks for taking time to reply :)
Feb 15 2019 02:14 AM
Hello xucaroline,
You may add Helper Columns C and D, which I labeled as DistinctID and TotalSales, respectively, in the attached file. The formulas in Column C sort the distinct NameIDs in Column A while those in Column D calculate the Total Sales for each distinct ID.
Then, the formulas in Column F return the top 6 amounts of Total Sales while those in Column E return the corresponding NameID for each amount of Total Sales.
Note that the named ranges are dynamic and no volatile functions were used in the formulas to expedite the calculation process.
Cheers!
Twifoo
Feb 15 2019 02:35 AM
Many thanks for taking time to look at it.
I am hoping for a solution that solves directly from the 2 columns, without to sort data or helper column or pivot.....
Feb 15 2019 03:04 AM
SolutionHello xucaroline,
By all means, your request is instantly granted! See it here in the attached file.
Cheers!
Twifoo
Feb 15 2019 03:57 AM
wow!
this is exactly what I am looking for, you are GREAT! thanks again!
Feb 15 2019 06:09 AM
is it possible to make the top 6 automatically updated with total sales and its name when data changes?
Feb 15 2019 07:50 AM
Feb 18 2019 12:19 AM
you are of course right! I am so embarrassed, was in the read only view.
Feb 19 2019 02:02 AM
Hi again
I have tried to use your solution/function(s), but it only gives me 0, as I do not understand the totsales part, could you be kind to explain me the steps in detail?
Feb 19 2019 02:27 AM
Feb 19 2019 02:57 AM
array formula total new for me, hehee.....
I will do some understanding and then see if I can manage re-use your functions (which is totally brilliant).
After I manage this step, I have a third column which is year (so I want to sort the largest by a specific year), I cannot even think how complicated that will be ( probably very easy for you, you just click here and click there - wish I was there at this level). I probably need to ask you for more help.
Feb 19 2019 03:04 AM
Aug 16 2019 05:32 AM
Aug 16 2019 11:11 PM - edited Aug 16 2019 11:12 PM
Hello @xucaroline,
After 6 months and 4 days, I'm delighted to hear from you again. In answer to your questions, it is feasible to automatically generate the top 6 values for the next 12 months and their corresponding Contract IDs, thereby rendering the EndYear column superfluous. In the attached file, the formula in I4, to return the top 6 values for the next 12 months, is:
=AGGREGATE(14,4,
ContractIDValues*(COUNTIF(I$3:I3,ContractIDValues)=0),
ROW()-3)
Conversely, the formula in H4, to return the Contract ID for each value, is:
=LOOKUP(2,
1/(ContractIDValues=I4),
ContractIDs)
Note that foregoing formulas use 4 defined names, as listed starting in H13.
Cheers,
Twifoo
Feb 15 2019 03:04 AM
SolutionHello xucaroline,
By all means, your request is instantly granted! See it here in the attached file.
Cheers!
Twifoo