How do I compare data for duplicated and missing entries

[ghrek]

Hi

I have a workbook that has data in columns A-E.

Depending on what data it is in column "A" is has the word "ABC" or "TAB" and then columns B-E all the specific data.

What im trying to do is if poss via a macro look all the way down the workbook for entries in "ABC" and "TAB".

The following criteria needs to be met.

If data in columns B-E an exact match in "ABC" and "TAB" nothing needs to be done.
If data in column B-E in "TAB" but not in "ABC" then I need it marked up "MISSING" in column F of the row concerned
If data in Column B-E in "ABC" duplicated then need the word "DUPLICATE" in column F of the row concerned
If data in Column B-E in "ABC" but not in "TAB" then I need the words "NO TAB DATA"


Is this possible?

 

[DanteAmor]

You could copy some examples of your sheet with the expected result and paste here.

 

[ghrek]

I found this on the internet and wondered if it does what I want it to do.

The code go thru the NEW sheet and searches for the data on the OLD Sheet.
If the data is found, it highlights both rows Green and add Match Found on both sheets
In the data is not found it will highlight the row on the NEW sheet Blue and add Not Found.

What I also need to do if poss is the same but the other way round too. If the data in the OLD sheet but not NEW sheet I need it to highlight the row on the NEW sheet Blue and add Not Found too.

What I need it to do is go across columns b-e on both sheets. Can anyone confirm for me or be able to amend please.


Sub B_CompareTrackingNumbers()




Sheets("NEW").Select


Range("A1").Select


ActiveCell.SpecialCells(xlLastCell).Select

Range("A" & ActiveCell.Row).Select
If Trim(Range("A" & ActiveCell.Row).Value) = "" Then
Do Until Range("A" & ActiveCell.Row).Value <> "" Or Range("B" & ActiveCell.Row).Value <> "" Or Range("C" & ActiveCell.Row).Value <> ""
Selection.Offset(-1, 0).Select
Loop
End If
strAnnex1FinalRowLatest = ActiveCell.Row


Range("A2").Select

Do Until ActiveCell.Row = strAnnex1FinalRowLatest + 1
intPreviousCompareLastRow = ActiveCell.Row

str_Latest_Tracking_Number = CStr(Trim(Range("A" & ActiveCell.Row).Value))
str_Part_Number = CStr(Trim(Range("B" & ActiveCell.Row).Value))
str_Part_Name = CStr(Trim(Range("C" & ActiveCell.Row).Value))
str_Quantity = Trim(Range("D" & ActiveCell.Row).Value)
str_Total_Mass = Trim(Range("E" & ActiveCell.Row).Value)
str_Latest_Date = Trim(Range("F" & ActiveCell.Row).Value)



If str_Latest_Tracking_Number = "" Then

Do Until str_Latest_Tracking_Number <> ""
Selection.Offset(1, 0).Select
str_Latest_Tracking_Number = Trim(Range("A" & ActiveCell.Row).Value)

Loop

End If


Sheets("OLD").Select
Range("A2").Select

On Error Resume Next


'THIS IS THE SEARCH CODE.
Cells.Find(What:=str_Latest_Tracking_Number, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate

intErrorNumber = Err.Number

If intErrorNumber = 91 Then 'FIX for error 91 If item is not found you will get Error 91,



Sheets("NEW").Select

Range("G" & ActiveCell.Row).Value = "Not Found"
Range("A" & ActiveCell.Row & ":G" & ActiveCell.Row).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2 'Light Blue
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Sheets("NEW").Select
Range("G" & ActiveCell.Row).Value = "NEW"

intErrorNumber = 0

ElseIf intErrorNumber = 0 Then 'FIX for error 91. If the item is found, it highlights the rows on both sheets green and aadd Match Found in the column of your choice.
'HIGHLIGHT BOTH SHEETS GREEN

Range("G" & ActiveCell.Row).Value = "Match Found"
Range("A" & ActiveCell.Row & ":G" & ActiveCell.Row).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3 'Light Green
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Range("A" & ActiveCell.Row).Select



Sheets("NEW").Select
Range("G" & ActiveCell.Row).Value = "Match Found"
Range("A" & ActiveCell.Row & ":G" & ActiveCell.Row).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3 'Light Green
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Range("A" & ActiveCell.Row).Select

End If

Selection.Offset(1, 0).Select

Loop

Range("A2").Select
ActiveWorkbook.Save

MsgBox "Done"

End Sub