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 |
|---|
|
|---|
| A | B | C | D | E |
|---|
| 1 | Group | Supplier | Product | Sales | Highest sales in |
|---|
| 2 | Grp1 | Supplier 1 | | 10 | Grp7 |
|---|
| 3 | Grp1 | Supplier 2 | | 15 | Grp2 |
|---|
| 4 | Grp1 | Supplier 2 | | 5 | Grp2 |
|---|
| 5 | Grp2 | Supplier 1 | | 15 | Grp7 |
|---|
| 6 | Grp2 | Supplier 2 | | 20 | Grp2 |
|---|
| 7 | Grp2 | Supplier 2 | | 15 | Grp2 |
|---|
| 8 | Grp3 | Supplier 1 | | 25 | Grp7 |
|---|
| 9 | Grp4 | Supplier 1 | | 5 | Grp7 |
|---|
| 10 | Grp4 | Supplier 2 | | 25 | Grp2 |
|---|
| 11 | Grp5 | Supplier 1 | | 15 | Grp7 |
|---|
| 12 | Grp6 | Supplier 1 | | 10 | Grp7 |
|---|
| 13 | Grp7 | Supplier 1 | | 15 | Grp7 |
|---|
| 14 | Grp7 | Supplier 1 | | 20 | Grp7 |
|---|
| 15 | Grp7 | Supplier 2 | | 10 | Grp2 |
|---|
|
|---|
----- --
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.
----- --
