Hi and thank you,
I'm trying to create a dynamic top ten list of units by department. I created the appropriate index(match) formula to create the list. The index(match) formula relies on providing a unique rank for the products in a specific department. The list will change as selling periods progress. The data originates from our SAP server and pulled into Excel via Analysis for Office add-on.
Using =rank(K3,$K$3:$K$706)+countif($K$3:$K3,K3)-1 I return the rank for all product regardless of department in column I while avoiding duplicate ranks, i.e. overall top ten. There is only one 1 in column I. Column D contains the department name and Column K contains the total units sold (values from which I base the rank).
Using ={sumproduct(($D$3:$D$706=D3)*(K3<$K$3:$K$706))+1} in column J, I can rank the units (column K) by department (column D), i.e. top ten for each department, etc, in column J.
The problem arises that this formula repeats ranks if two values are identical. I've sorted the data in the upload file by columns D (Department) then J (rank within Department) so I could run an error check. There are quite a few occurrences where a rank was duplicated.
The data cannot be sorted prior to determining the rank. Nor is adding a helper column an option. Lastly, I don't use VBA. Any suggestions for a formula would be appreciated.
Thanks.
I'm trying to create a dynamic top ten list of units by department. I created the appropriate index(match) formula to create the list. The index(match) formula relies on providing a unique rank for the products in a specific department. The list will change as selling periods progress. The data originates from our SAP server and pulled into Excel via Analysis for Office add-on.
Using =rank(K3,$K$3:$K$706)+countif($K$3:$K3,K3)-1 I return the rank for all product regardless of department in column I while avoiding duplicate ranks, i.e. overall top ten. There is only one 1 in column I. Column D contains the department name and Column K contains the total units sold (values from which I base the rank).
Using ={sumproduct(($D$3:$D$706=D3)*(K3<$K$3:$K$706))+1} in column J, I can rank the units (column K) by department (column D), i.e. top ten for each department, etc, in column J.
The problem arises that this formula repeats ranks if two values are identical. I've sorted the data in the upload file by columns D (Department) then J (rank within Department) so I could run an error check. There are quite a few occurrences where a rank was duplicated.
The data cannot be sorted prior to determining the rank. Nor is adding a helper column an option. Lastly, I don't use VBA. Any suggestions for a formula would be appreciated.
Thanks.
