4th criteria in COUNTIFS

[Tommeck37]

Hello,

Can someone help with the below code?
This is a macro that finds duplicates based on three criteria.

Is it possible to add another criteria but not duplicate but a difference. I need a code to find the same account number (criteria 1), the same amount (criteria 2), the same value date (criteria 3) and 4th criteria: information in first duplicate is different from information in corresponding duplicate.
The information in 4th criteria column is always "0" or "1". To solve the problem, the two items should have all the above 3 criteria the same, and one item "0" and the other "1". If both have "0" or "1" macro should not find it.

this is the code

Sub MatchingItems()Dim LastRow As Long, DatesCol As Long, AmountsCol As Long, NostroCol As Long
LastRow = Sheets(1).Range("E" & Sheets(1).Rows.Count).End(xlUp).Row
DatesCol = 6 'D column with dates
AmountsCol = 5 'E column with amounts
NostroCol = 1 ' A column with account number
'Columns(DatesCol).Interior.ColorIndex = xlNone 'dates lose color


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Sheets(1).Activate
With Sheets(1)
For i = 1 To LastRow 'for each row
If 2 <= Application.WorksheetFunction.CountIfs(Range(Cells(1, DatesCol), Cells(LastRow, DatesCol)), _
                  Cells(i, DatesCol), _
                  Range(Cells(1, AmountsCol), Cells(LastRow, AmountsCol)), _
                  Cells(i, AmountsCol), _
                  Range(Cells(1, NostroCol), Cells(LastRow, NostroCol)), _
                  Cells(i, NostroCol)) _
                  Then Cells(i, 5).Interior.ColorIndex = 3 ' 'counts the date values associated with occurrences if there's more than one then the date gets a nice background color
Next i


End With
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic


MsgBox ("Matching Items Put In Red")
End Sub

Thank you in advance

Tommeck37

 

[gruntingmonkey]

Did you find the answer for this? I can only do it a really long winded way and wondered if you had sorted a smooth fast way out?

 

[Tommeck37]

Unfortunatelly not yet. Can you please show me your way?

 

[gruntingmonkey]

So in column I, I have put a formula in
=CONCATENATE(G1,H1)
and dragged it down.
In column H, the code now marks where there is a match
In column G should be your 0 & 1's.

Sub MatchingItems()
Dim LastRow As Long, DatesCol As Long, AmountsCol As Long, NostroCol As Long, calccol As Long

LastRow = Sheets(1).Range("E" & Sheets(1).Rows.Count).End(xlUp).Row
DatesCol = 6 'F column with dates
AmountsCol = 5 'E column with amounts
NostroCol = 1 ' A column with account number
calccol = 8 'H for calculating
'Columns(DatesCol).Interior.ColorIndex = xlNone 'dates lose color


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Sheets(1).Activate
With Sheets(1)
For i = 1 To LastRow 'for each row
If 2 <= Application.WorksheetFunction.CountIfs(Range(Cells(1, DatesCol), Cells(LastRow, DatesCol)), _
                  Cells(i, DatesCol), _
                  Range(Cells(1, AmountsCol), Cells(LastRow, AmountsCol)), _
                  Cells(i, AmountsCol), _
                  Range(Cells(1, NostroCol), Cells(LastRow, NostroCol)), _
                  Cells(i, NostroCol)) _
                  Then Cells(i, 5).Interior.ColorIndex = 3
                  
                    If Cells(i, AmountsCol).Interior.ColorIndex = 3 Then
                     Cells(i, calccol).Value = 1
                    End If
Next i

For i = 1 To LastRow 'for each row
If 2 <= Application.WorksheetFunction.CountIfs(Range(Cells(1, 9), Cells(LastRow, 9)), _
                  Cells(i, 9), _
                  Range(Cells(1, AmountsCol), Cells(LastRow, AmountsCol)), _
                  Cells(i, AmountsCol), _
                  Range(Cells(1, NostroCol), Cells(LastRow, NostroCol)), _
                  Cells(i, NostroCol)) _
                  Then Cells(i, 5).Interior.ColorIndex = 5
                  
Next i

End With
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic


MsgBox ("Matching Items Put In blue, Matching except last col is red")
End Sub

This Way you have Red and Blue Match's depending on what you want.

 

[Tommeck37]

Can you please explain to me what pieces of information you would like to concatanate in column I?
I am asking because in G there is info totally different from our discussion, which does not affect searching, and in H there is either "0" or "1".

And one more question, why are you seraching column 9? There is information that does not affect searching criteria.

 

[gruntingmonkey]

Column I is a concatenation of

Your 1 / 0 (4th criteria column) & a new 5th criteria column that is required for this to work - Marks matches as 1

Also, Column 9 (Column I) should have the concatenation sum in there.

Remember you may have to move the coding about if you have stuff in columns that you didn't make us aware of.

Is that any clearer? Apologies, I'm not great at explaining stuff yet!