asked on

How do I compare data on 2 worksheets in same workbook

Hi

I have a workbook of which has data in sheets 1 & 2 and im trying to compare the two to look for missing data.

Data will be in columns A-D in both sheets . What im trying to do is look in sheet 1 and try and match up with what in sheet 2.
Where its a match in sheets 1&2 I want to put in column E in sheet 1 as OK and if there is no match I want it put as MISSING in column E.

Issues I have are that although the data may all be there in sheets 1 & 2 they will not be in the same row on both sheets. I.E data may be in row 14 in sheet 1 and the same in row 99 on sheet 2 so I need it to look all the way down sheet 2 to verify.
Other issues are that in in column C on both sheets there may be times when the date is different. I.E in sheet 1 may be shown as 24/04/2019 and in sheet 2 may be 24 Apr 19. Is there anyway that I can overcome this.

Can this this be done via macro or some other way as I have over 1000 entries on both sheets.

Thanks in advance

Arana (G.P.)

You can use vlookup from cell E in sheet 1 to look for a value in sheet 2 , you will have to ignore the date, but you must use a column that can match in both sheets (column A maybe?)

how many columns of data? can you provide a sample of the data?

Roy Cox

You can use VLOOKUP or INDEX?MATCH formulas, it depends on the data layout. Can you attach an example with dummy data? The dates shouldn't be a problemif they are entered as dates and not as text

david jones

ASKER

Here goes I have the workbook and you see that all the data is in sheets 1&2 but in different locations.

As stated if is in sheets 1 &2 I need it put as OK or missing on sheet 1.

ALL the data in the row must agree on both sheets to be OK.

Hope that helps
COMPARE.xlsx

Roy Cox

Are the blank rows typical?

Does each entry for NLC along the row need to match, or if the NLC exists will the data along the row match?

david jones

ASKER

Blank rows not typical sorry.

Each entry of NLC the data along the row must match with what in the other sheet if that makes sense.

Roy Cox

If each entry in the row needs to match and they might be different then a helper column could be used.

david jones

ASKER

Ok. Sorry helper column never heard of that what u mean?

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

A helper column would combine the contents of each cell in the row and them match them in each sheet. I'll add an example

david jones

ASKER

This formula works good and wondered if i can add to it so it can highlight duplicated entries in sheet 2 too.

Sometimes I get the same data put in twice in workbook 2 but I think it may come up OK in workbook 1 of which it is but not duplicates.

formula

=IF($A2="","",IF(COUNTIFS(Sheet2!$A$2:$A$10000,$A2,Sheet2!$B$2:$B$10000,$B2,Sheet2!$C$2:$C$10000,$C2,Sheet2!$D$2:$D$10000,$D2),"OK","MISSING"))

Ejgil Hedegaard

You could just use the countif part of the formula.
So every count that is not 1 (or 0 when NLC is empty) is an error.
See column F.

Or use conditional formatting.
Added yellow if missing, and orange if duplicate.

On Sheet2 duplicates in column A are yellow.
COMPARE.xlsx

Ejgil Hedegaard

You should close the question by accepting one or more of the answers.

WonHop

This 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.
I am searching using str_Latest_Tracking_Number.

To test this code, put the matching data in Column A and both sheets.
You will have to change it to the columns you need to use later.
Click on F8 to step thru the code.
Use as much of this code you need and comment out the rest until you feel comfortable to delete the unneeded code.

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

Thanks
WonHop

Open in new window