Solved: Determine if a set of records match

[JimS]

I need to determine if a set of records match based on 2 criteria.

In the attachment is an example showing multiple invoices and their associated dates.

At the individual invoice number I need to determine if it has more than 1 Date and highlight it.

Different invoices numbers could have the same date as other invoices.

I have manually hightight the Invoices that meet the criteria of having more than 1 date.

I just happen to use multiple colors just to show the different sets for this example.

Any ideas?

Thanks...

JimS

[Trebor76]

Hi JinS,

See how this goes:


Option Explicit
Sub Macro1()

'Written by Trebor76
'Visit my website www.excelguru.net.au (http://www.excelguru.net.au)

Dim rngCell As Range
Dim lngEndRow As Long
Dim varInvNo As Variant, _
varInvDate As Variant

Application.ScreenUpdating = False

lngEndRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For Each rngCell In Range("A2:A" & lngEndRow)
'Set the invoice number and date to the first new invoice number and date
If IsEmpty(varInvNo) = True And IsEmpty(varInvDate) = True Then
varInvNo = Range("A" & rngCell.Row)
varInvDate = Range("B" & rngCell.Row)
ElseIf varInvNo <> Range("A" & rngCell.Row) And varInvDate <> Range("B" & rngCell.Row) Then
varInvNo = Range("A" & rngCell.Row)
varInvDate = Range("B" & rngCell.Row)
End If
'Fill the cells (currently in yellow) if the current invoice date is different from the the 'varInvDate' variable
If Range("A" & rngCell.Row) = varInvNo And Range("B" & rngCell.Row) <> varInvDate Then
Range("A" & rngCell.Row - 1 & ":B" & rngCell.Row - 1).Interior.Color = RGB(255, 255, 0)
Range("A" & rngCell.Row & ":B" & rngCell.Row).Interior.Color = RGB(255, 255, 0)
End If
Next rngCell

Application.ScreenUpdating = True

End Sub

Regards,

Robert

[JimS]

Robert,

Thanks for your code.

It's very close but misses some of the records within a few of the individual sets of records, and it missed 2 sets altogether.

I attached the results of the code showing the misses.
JimS

[JimS]

It has to do with the sequnce of the dates. Need to somehow look at all the dates at once for a set of records instead of just the previous date. There could be 2 alike and then the 3rd date could be different for the same set of invoice numbers.

Could we use a helper column somehow and then highlight the records based on the helper column value (ie: >1)?

[Trebor76]

See how this goes:


Option Explicit
Sub Macro2()
'Written by Trebor76
'Visit my website www.excelguru.net.au (http://www.excelguru.net.au)
Dim lngLastRow As Long, _
lngMyCol As Long
Dim strMyCol As String
Dim rngCell As Range
Dim lngDelCol As Long
lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1

Application.ScreenUpdating = False

Range(Cells(2, lngMyCol), Cells(lngLastRow, lngMyCol)).Formula = "=A2&B2"
strMyCol = Left(Cells(1, lngMyCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngMyCol).Address(True, False)) - 1)
lngMyCol = lngMyCol + 1
Range(Cells(2, lngMyCol), Cells(lngLastRow, lngMyCol)).Formula = "=COUNTIF($" & strMyCol & "$2:$" & strMyCol & "$" & lngLastRow & "," & strMyCol & "2)"
lngMyCol = lngMyCol + 1
Range(Cells(2, lngMyCol), Cells(lngLastRow, lngMyCol)).Formula = "=COUNTIF($A$2:$A$" & lngLastRow & ",A2)"

For Each rngCell In Range("A2:A" & lngLastRow)
If (Cells(rngCell.Row, lngMyCol - 1)) <> Cells(rngCell.Row, lngMyCol) Then
Range("A" & rngCell.Row & ":B" & rngCell.Row).Interior.Color = RGB(146, 208, 80)
End If
Next rngCell

'Delete helper columns
For lngDelCol = lngMyCol To lngMyCol - 2 Step -1
Columns(lngDelCol).EntireColumn.Delete
Next lngDelCol

Application.ScreenUpdating = True

End Sub

Regards,

Robert

[JimS]

Robert,

Perfect - Thank you very much...

JimS

[Trebor76]

Thanks for letting us know and you're welcome.