How do I compare data for duplicated and missing entries

ghrek

Active Member
Joined
Jul 29, 2005
Messages
426
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You could copy some examples of your sheet with the expected result and paste here.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top