SOLVED

Excel

Copper Contributor

 

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!

19 Replies

You may pivot your table and filer it on top 6, please see attached.

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 :)

 

Hi,

 

Perhaps it's possible, but VBA is not my expertise.

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

 

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.....

best response confirmed by xucaroline (Copper Contributor)
Solution

Hello xucaroline,

 

By all means, your request is instantly granted! See it here in the attached file. 

 

Cheers!

 

Twifoo

wow!

this is exactly what I am looking for, you are GREAT! thanks again!

 

is it possible to make the top 6 automatically updated with total sales and its name when data changes?

As indicated earlier, the named ranges are dynamic and so is the named formula. Inevitably, the top 6 results automatically update as new data are added or old data are deleted. Prove it!

you are of course right!  I am so embarrassed, was in the read only view.

 

You don't need to be embarrassed. It was my pleasure to help you!

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?

Hello Caroline,
TotSales refers to this named array formula:
=SUMIF(NameID,NameID,Sales)
The formula returns an array of Total Sales for each NameID. To avoid the use of Helper Columns, it is used as part of the lookup_vector argument in the formulas for Top 6 Sales and Top 6 NameIDs, and as the result_vector argument of the formulas for Top 6 Sales.
Stated differently, TotSales is the equivalent of the 2 Helper Columns you wanted to avoid.

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.

I learned array formulas from this book:
https://www.mrexcel.com/store/index.php?l=product_detail&p=263
If you need my help, please don't hesitate to ask me.

thanks a lot, again!

hi hi again I am adding one more criteria, and wonder if this is feasible at all. In column D I have EndDate (date that the contract is ending), still same a previous ad before with multiple ContractID in column A. 1. I would like to have a solution that automatically generates top 6 ContractID name and value ending the coming next 12 month listed from the largest. If this is not feasible 2. I would like to use column C, EndYear. Will it be ok to have it to the right of the Value/column B or does it have to be on the left side of Value/column B? Although I figured out your last solution, it is still a little complicated with that many functions. I guess I need to use Formulas- name manger to do the necessary calculations instead of the helper columns first.

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

Hi@Twifoo 

 

I am so happy to receive answer from you, and in that super fast speed, thank you so much for taking time to help me. I will look into and try to understand the answer. 

 

Best regards

Caroline

1 best response

Accepted Solutions
best response confirmed by xucaroline (Copper Contributor)
Solution

Hello xucaroline,

 

By all means, your request is instantly granted! See it here in the attached file. 

 

Cheers!

 

Twifoo

View solution in original post