Code to Determine The Largest Number From Different Groups of Numbers

[myghetto]

Hi,

I have a basic knowledge of VBA and Excel. I have been trying to write a code to determine this:

Col1.....Col2.....Col3.....Col4.....Col5 (Y)
ABC.....F123.....160.....120
ABC.....F123.....120.....120
ABC.....F125.....110.....120
BCB.....F125.....58.....160
BCB.....F125.....55.....120
BCB.....F127.....56.....120

Basically I am looking for a code to determine the largest number from Column 3 (Col3) based on the two different groups in Column 1 (Col1) and the sub-group Column 2 (Col2). ABC is in the same group, Column 2 is the subgroup. IF there in the group Column 1, there are two F123, then I want to compare the numbers in Column 3 to find the biggest number. In this case it's 160. I want to be able to put a "Y" in Column 5 in the same row as the number 160 to show its the biggest. The ACB F125 is by itself so it is automatically accepted as the largest number and a "Y" will be inserted in Column 5 in the same row. Then I want the code to look at the next group BCB. Since there are two F125s again, we compare the 58 and 55. Obviously 58 is larger but I want the difference of more than 10 to be considered so since 58 and 55 is a difference of 3, we look at the next column, Column 4. Here we see 160 is bigger than 120 so a Y will be put in the BCB F125 58 160 row. I would really appreciate it if someone could help me out as I have tried to write it the inefficient way and its still not working. Thank you in advance.

[Bob Phillips]

Try this array formula

=IF(SUM(($A$1:$A$20=A1)*($B$1:$B$20=B1))=1,"Y",
IF(MAX(IF(($A$1:$A$20=A1)*($B$1:$B$20=B1),$C$1:$C$20))-LARGE(IF(($A$1:$A$20=A1)*($B$1:$B$20=B1),$C$1:$C$20),2)>=10,
IF(MAX(IF(($A$1:$A$20=A1)*($B$1:$B$20=B1),$C$1:$C$20))=$C1,"Y",""),
IF(MAX(IF(($A$1:$A$20=A1)*($B$1:$B$20=B1),$D$1:$D$20))=$D1,"Y","")))

[myghetto]

Thanks xld. I am actually looking for the VBA code equivalent. I should have mentioned that earlier. Please have a look at the code below:

[VBA]
Sub Feeder
Dim Rng As Range
Dim Dn As Range
Dim Twn As String
Dim K
Dim oMax As Range
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Rng.Offset(, 4).ClearContents
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
Twn = Dn & Dn.Offset(, 1)
If Not .Exists(Twn) Then
.Add Twn, Dn
Else
Set .Item(Twn) = Union(.Item(Twn), Dn)
End If
Next
For Each K In .Keys
If .Item(K).Count > 1 Then
If Application.max(.Item(K).Offset(, 2)) - Application.Large(.Item(K).Offset(, 2), 2) >= 10 Then
For Each oMax In .Item(K).Offset(, 2)
If oMax = Application.max(.Item(K).Offset(, 2)) Then
oMax.Offset(, 2) = "Y"
End If
Next oMax
ElseIf Application.max(.Item(K).Offset(, 3)) - Application.Large(.Item(K).Offset(, 3), 2) >= 10 Then
For Each oMax In .Item(K).Offset(, 3)
If oMax = Application.max(.Item(K).Offset(, 3)) Then
oMax.Offset(, 1) = "Y"
End If
Next oMax
End If
ElseIf .Item(K).Count = 1 Then
.Item(K).Offset(, 4) = "Y"
End If
Next K
End With
End Sub[/VBA]

What I am trying to do now:

I added another column between Col 4 and Col 5.

Col1.....Col2.....Col3.....Col4.....Col5 ....Col6(Y)
ABC.....F123.....160.....120......65
ABC.....F123.....120.....120......65
ABC.....F125.....110.....120......65
BCB.....F125.....58.....120......65
BCB.....F125.....55.....120
BCB.....F127.....56.....120......65

Notice how theres a gap there. If I want the code to compare the two BCBF125 and decide that the bigger the number (>10) the better but if there is a blank, that is better than any of the largest number available. So in this case, the second BCBF125 is better than the first. Please help. Thank you in advance.

I found that every time this code hits a gap, a mismatch type 13 error comes up. I can't seem to figure out how to get rid of this problem. Please help.

[Bob Phillips]

I don't get that error when I run it, but it does verwrite column E (the 65s).