Advise needed!!! Comparing across 2 coulumns of dates and returning the highest score of the most recent date

[yuenbless]

Hi all, need some help here.

Sample data:

<TABLE style="WIDTH: 365pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=486 border=0 x:str><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=84 height=51>Acct</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=63>Open Dte</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=66>Score Dte</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=68>Score</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 79pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=105>Max Score</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 75pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: lime" width=100>
Result that I got
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A001</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39556">18-Apr-08</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39556">18-Apr-08</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>214</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>214</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: lime" align=right x:num x:arrayrange="F2" x:fmla="=MAX(IF($A$2:$A$7=A2,IF($B$2:$B$7=MAX(IF($A$2:$A$7=A2,$B$2:$B$7)),IF($C$2:$C$7=MAX(IF($A$2:$A$7=A2,$C$2:$C$7)),$D$2:$D$7))))">0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A001</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39555">17-Apr-08</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39560">22-Apr-08</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>141</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>214</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: lime" align=right x:num x:arrayrange="F3" x:fmla="=MAX(IF($A$2:$A$7=A3,IF($B$2:$B$7=MAX(IF($A$2:$A$7=A3,$B$2:$B$7)),IF($C$2:$C$7=MAX(IF($A$2:$A$7=A3,$C$2:$C$7)),$D$2:$D$7))))">0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B002</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39631">2-Jul-08</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39664">4-Aug-08</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>165</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>178</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: lime" align=right x:num x:arrayrange="F4" x:fmla="=MAX(IF($A$2:$A$7=A4,IF($B$2:$B$7=MAX(IF($A$2:$A$7=A4,$B$2:$B$7)),IF($C$2:$C$7=MAX(IF($A$2:$A$7=A4,$C$2:$C$7)),$D$2:$D$7))))">178</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B002</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39631">2-Jul-08</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39709">18-Sep-08</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>178</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>178</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: lime" align=right x:num x:arrayrange="F5" x:fmla="=MAX(IF($A$2:$A$7=A5,IF($B$2:$B$7=MAX(IF($A$2:$A$7=A5,$B$2:$B$7)),IF($C$2:$C$7=MAX(IF($A$2:$A$7=A5,$C$2:$C$7)),$D$2:$D$7))))">178</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C003</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39387">1-Nov-07</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39388">2-Nov-07</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>300</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>500</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: lime" align=right x:num x:arrayrange="F6" x:fmla="=MAX(IF($A$2:$A$7=A6,IF($B$2:$B$7=MAX(IF($A$2:$A$7=A6,$B$2:$B$7)),IF($C$2:$C$7=MAX(IF($A$2:$A$7=A6,$C$2:$C$7)),$D$2:$D$7))))">500</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C003</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39387">1-Nov-07</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="39388">2-Nov-07</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>500</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" align=right x:num>500</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: lime" align=right x:num x:arrayrange="F7" x:fmla="=MAX(IF($A$2:$A$7=A7,IF($B$2:$B$7=MAX(IF($A$2:$A$7=A7,$B$2:$B$7)),IF($C$2:$C$7=MAX(IF($A$2:$A$7=A7,$C$2:$C$7)),$D$2:$D$7))))">500</TD></TR></TBODY></TABLE>

I am trying to achieve the the following:

For records with the same Acct:

1st Level check:
Look for the record with the latest Open dte return the Score as the Max score (yellow column) for all records under the same Acct.

2nd Level Check:
If there are more than 1 record with the same latest Open date, to check the Score dte and return the Score under the latest Score Dte.

3rd Level Check:
If there are more than 1 record with the same latest Open Dte and same latest Score dte, to take the highest score.


The Max Score (Yellow column) will be the same for all records under the same Acct.


I tried the array formula but something is wrong somewhere. I got 0 for the first 2 records instead of the expected value of 214

=MAX(IF($A$2:$A$7=A2,IF($B$2:$B$7=MAX(IF($A$2:$A$7=A2,$B$2:$B$7)),IF($C$2:$C$7=MAX(IF($A$2:$A$7=A2,$C$2:$C$7)),$D$2:$D$7)))))


Can Anyone enlighten me here? Thanks.....

 

[MickG]

Hi, This may not be what you want, But it should work as long as there are only ever pairs of accounts.

Sub acct()
Dim Rng As Range, Dn As Range, C As Long
Dim Mx As Long, MxNum As Integer

Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For Each Dn In Rng
      If Not .Exists(Dn.Value) Then
          C = C + 1
           .Add Dn.Value, Array(Dn.Offset(, 1), Dn.Offset(, 2), Dn.Offset(, 3), Dn.Row)
      Else
       If Not .Item(Dn.Value)(0) = Dn.Offset(, 1) Then
          Mx = Application.Max(Dn.Offset(, 1), .Item(Dn.Value)(0))
            If Dn.Offset(, 1) = Mx Then
                 MxNum = Dn.Offset(, 3)
              Else
                MxNum = .Item(Dn.Value)(2)
              End If
                   Cells(.Item(Dn.Value)(3), "E") = MxNum
                    Dn.Offset(, 4) = MxNum

      ElseIf .Item(Dn.Value)(0) = Dn.Offset(, 1) And Not .Item(Dn.Value)(1) = Dn.Offset(, 2) Then
         Mx = Application.Max(Dn.Offset(, 2), .Item(Dn.Value)(1))
           If Dn.Offset(, 2) = Mx Then
                MxNum = Dn.Offset(, 3)
           Else
                MxNum = .Item(Dn.Value)(2)
           End If
               Cells(.Item(Dn.Value)(3), "E") = MxNum
               Dn.Offset(, 4) = MxNum
      ElseIf .Item(Dn.Value)(0) = Dn.Offset(, 1) And .Item(Dn.Value)(1) = Dn.Offset(, 2) Then
        Mx = Application.Max(Dn.Offset(, 3), .Item(Dn.Value)(2))
               Cells(.Item(Dn.Value)(3), "E") = Mx
               Dn.Offset(, 4) = Mx

      End If
    End If
 Next
End With
End Sub

Regards Mick

 

[yuenbless]

Hi Mick,

Thanks for your suggestion but records for each account may be more than 2, so what you suggested may not work.


any other suggestions?

 

[MickG]

Hi Try this, it seems to work over my limited testing.

Sub acctMkII()
Dim Rng As Range, Dn As Range, C As Long
Dim Mx As Long, MxNum As Integer, q
C = 0
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
 For Each Dn In Rng
   If Not .Exists(Dn.Value) Then
        .Add Dn.Value, Array(Dn.Offset(, 1), Dn.Offset(, 2), Dn.Offset(, 3), C)
   Else
      q = .Item(Dn.Value)
        If Not .Item(Dn.Value)(0) = Dn.Offset(, 1) Then
          Mx = Application.Max(Dn.Offset(, 1), .Item(Dn.Value)(0))
            If Dn.Offset(, 1) = Mx Then
                 MxNum = Dn.Offset(, 3)
              q(0) = Dn.Offset(, 1): q(1) = Dn.Offset(, 2): q(2) = Dn.Offset(, 3): q(3) = MxNum
              Else
                MxNum = .Item(Dn.Value)(2)
              End If
                .Item(Dn.Value) = q
       ElseIf .Item(Dn.Value)(0) = Dn.Offset(, 1) And Not .Item(Dn.Value)(1) = Dn.Offset(, 2) Then
         Mx = Application.Max(Dn.Offset(, 2), .Item(Dn.Value)(1))
           If Dn.Offset(, 2) = Mx Then
                MxNum = Dn.Offset(, 3)
           q(0) = Dn.Offset(, 1): q(1) = Dn.Offset(, 2): q(2) = Dn.Offset(, 3): q(3) = MxNum
           Else
                MxNum = .Item(Dn.Value)(2)
           End If
                .Item(Dn.Value) = q
      ElseIf .Item(Dn.Value)(0) = Dn.Offset(, 1) And .Item(Dn.Value)(1) = Dn.Offset(, 2) Then
         Mx = Application.Max(Dn.Offset(, 3), .Item(Dn.Value)(2))
         q(0) = Dn.Offset(, 1): q(1) = Dn.Offset(, 2): q(2) = Dn.Offset(, 3): q(3) = Mx
         .Item(Dn.Value) = q
     End If
   End If
 Next Dn

Dim oNum
For Each oNum In .keys
    For Each Dn In Rng
        If Dn = oNum Then
            Dn.Offset(, 4) = .Item(oNum)(3)
        End If
    Next Dn
Next oNum
End With
End Sub

Regards Mick