Formula to find group with highest sales per supplier

JV0710

Active Member
Joined
Oct 26, 2006
Messages
440
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Good Evening All
Please can I get some help with a formula :

In the view below I would like a formula in column E that sums the sales by Supplier (column B) in each group (column A) and returns the Group where the highest sales occurred for each supplier, irrespective of product (column c). So, as the table below column E has "Grp3" for all rows with supplier 1 and Grp 2 for all rows with supplier 2 etc

Thanks in advance

1676048573017.png
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try the following UDF.

Put the following code in a module:

VBA Code:
Function FindGroup(data As Range, supplier As String) As String
'find group with highest sales per supplier
  Dim dic As Object
  Dim a As Variant
  Dim i As Long
  Dim highsupp As String
  Dim nMax As Double
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = data.Value
  For i = 1 To UBound(a, 1)
    If a(i, 2) = supplier Then
      dic(a(i, 1)) = dic(a(i, 1)) + a(i, 4)
      If dic(a(i, 1)) > nMax Then
        nMax = dic(a(i, 1))
        highsupp = a(i, 1)
      End If
    End If
  Next
  FindGroup = highsupp
End Function

HOW TO INSTALL UDFs
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use FindGroup just like it was a built-in Excel function. For example:

(In fact, in your example, the group with the most sales for supplier1 is grp7, its sales add up to 35.)

Dante Amor
ABCDE
1GroupSupplierProductSalesHighest sales in
2Grp1Supplier 110Grp7
3Grp1Supplier 215Grp2
4Grp1Supplier 25Grp2
5Grp2Supplier 115Grp7
6Grp2Supplier 220Grp2
7Grp2Supplier 215Grp2
8Grp3Supplier 125Grp7
9Grp4Supplier 15Grp7
10Grp4Supplier 225Grp2
11Grp5Supplier 115Grp7
12Grp6Supplier 110Grp7
13Grp7Supplier 115Grp7
14Grp7Supplier 120Grp7
15Grp7Supplier 210Grp2
Hoja2
Cell Formulas
RangeFormula
E2:E15E2=FindGroup($A$2:$D$15,B2)


----- --
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
----- --
 
Last edited:
Upvote 1
Try the following UDF.

Put the following code in a module:

VBA Code:
Function FindGroup(data As Range, supplier As String) As String
'find group with highest sales per supplier
  Dim dic As Object
  Dim a As Variant
  Dim i As Long
  Dim highsupp As String
  Dim nMax As Double
 
  Set dic = CreateObject("Scripting.Dictionary")
  a = data.Value
  For i = 1 To UBound(a, 1)
    If a(i, 2) = supplier Then
      dic(a(i, 1)) = dic(a(i, 1)) + a(i, 4)
      If dic(a(i, 1)) > nMax Then
        nMax = dic(a(i, 1))
        highsupp = a(i, 1)
      End If
    End If
  Next
  FindGroup = highsupp
End Function

HOW TO INSTALL UDFs
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use FindGroup just like it was a built-in Excel function. For example:

(In fact, in your example, the group with the most sales for supplier1 is grp7, its sales add up to 35.)

Dante Amor
ABCDE
1GroupSupplierProductSalesHighest sales in
2Grp1Supplier 110Grp7
3Grp1Supplier 215Grp2
4Grp1Supplier 25Grp2
5Grp2Supplier 115Grp7
6Grp2Supplier 220Grp2
7Grp2Supplier 215Grp2
8Grp3Supplier 125Grp7
9Grp4Supplier 15Grp7
10Grp4Supplier 225Grp2
11Grp5Supplier 115Grp7
12Grp6Supplier 110Grp7
13Grp7Supplier 115Grp7
14Grp7Supplier 120Grp7
15Grp7Supplier 210Grp2
Hoja2
Cell Formulas
RangeFormula
E2:E15E2=FindGroup($A$2:$D$15,B2)


----- --
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
----- --
Thanks for your reply Dante - I will try the VBA code

I am also posing a mini sheet via xl2BB add-in
 
Upvote 0
find grp with high sales.xlsx
ABCDE
1GroupSupplierproductSaleshighest sales in
2Grp1Supplier 1product 110Grp7
3Grp1Supplier 2product 115Grp2
4Grp1Supplier 2product25Grp2
5Grp2Supplier 1product 115Grp7
6Grp2Supplier 2product 120Grp2
7Grp2Supplier 2product215Grp2
8Grp3Supplier 1product 125Grp7
9Grp4Supplier 1product 15Grp7
10Grp4Supplier 2product 125Grp2
11Grp5Supplier 1product 115Grp7
12Grp6Supplier 1product 110Grp7
13Grp7Supplier 1product 115Grp7
14Grp7Supplier 1product220Grp7
15Grp7Supplier 2product 110Grp2
Sheet1
 
Last edited:
Upvote 0
Thanks for your reply Dante - I will try the VBA code

I am also posing a mini sheet via xl2BB add-in
The VBA code for the UDF works perfectly Dante - Thanks for your answer. I am still trying to get a formula solution if possible

Thanks
 
Upvote 1
For 365 how about
Fluff.xlsm
ABCDE
1GroupSupplierproductSaleshighest sales in
2Grp1Supplier 1product 110Grp7
3Grp1Supplier 2product 115Grp2
4Grp1Supplier 2product25Grp2
5Grp2Supplier 1product 115Grp7
6Grp2Supplier 2product 120Grp2
7Grp2Supplier 2product215Grp2
8Grp3Supplier 1product 125Grp7
9Grp4Supplier 1product 15Grp7
10Grp4Supplier 2product 125Grp2
11Grp5Supplier 1product 115Grp7
12Grp6Supplier 1product 110Grp7
13Grp7Supplier 1product 115Grp7
14Grp7Supplier 1product220Grp7
15Grp7Supplier 2product 110Grp2
Data
Cell Formulas
RangeFormula
E2:E15E2=LET(u,UNIQUE($A$2:$A$15),TAKE(SORTBY(u,SUMIFS(D:D,B:B,B2,A:A,u),-1),1))
 
Upvote 1
For 365 how about
Fluff.xlsm
ABCDE
1GroupSupplierproductSaleshighest sales in
2Grp1Supplier 1product 110Grp7
3Grp1Supplier 2product 115Grp2
4Grp1Supplier 2product25Grp2
5Grp2Supplier 1product 115Grp7
6Grp2Supplier 2product 120Grp2
7Grp2Supplier 2product215Grp2
8Grp3Supplier 1product 125Grp7
9Grp4Supplier 1product 15Grp7
10Grp4Supplier 2product 125Grp2
11Grp5Supplier 1product 115Grp7
12Grp6Supplier 1product 110Grp7
13Grp7Supplier 1product 115Grp7
14Grp7Supplier 1product220Grp7
15Grp7Supplier 2product 110Grp2
Data
Cell Formulas
RangeFormula
E2:E15E2=LET(u,UNIQUE($A$2:$A$15),TAKE(SORTBY(u,SUMIFS(D:D,B:B,B2,A:A,u),-1),1))
Thank you for your answer Fluff. I have 365 but not the "Let" function (Work machine)
 
Upvote 0
Ok, how about
Excel Formula:
=TAKE(SORTBY(UNIQUE($A$2:$A$15),SUMIFS(D:D,B:B,B2,A:A,UNIQUE($A$2:$A$15)),-1),1)
 
Upvote 1
Ok, how about
Excel Formula:
=TAKE(SORTBY(UNIQUE($A$2:$A$15),SUMIFS(D:D,B:B,B2,A:A,UNIQUE($A$2:$A$15)),-1),1)
Thanks for this, but the "Take" function also does not work on my machine - I think I am going to ask IT to see if I can get the Latest upgrades
 
Upvote 0
the "Take" function also does not work on my machine
Not a problem, try
Excel Formula:
=INDEX(SORTBY(UNIQUE($A$2:$A$15),SUMIFS(D:D,B:B,B2,A:A,UNIQUE($A$2:$A$15)),-1),1)
 
Upvote 1
Solution

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top