02-12-2019 04:59 AM
02-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.
02-13-2019 12:32 AM
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 :)
02-15-2019 02:14 AM
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.
02-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.....
02-15-2019 03:04 AMSolution
By all means, your request is instantly granted! See it here in the attached file.
02-15-2019 07:50 AM
02-19-2019 02:02 AM
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?
02-19-2019 02:27 AM
02-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.
02-19-2019 03:04 AM
08-16-2019 05:32 AM
08-16-2019 11:11 PM - edited 08-16-2019 11:12 PM
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:
Conversely, the formula in H4, to return the Contract ID for each value, is:
Note that foregoing formulas use 4 defined names, as listed starting in H13.