excel vba comparing two sheets based on unique value

Question

I have 2 sheets named orange book data alerts and total orange book data alerts , both sheets have data from A to M, unique value is in Column A in both the sheets. here I compare orange book data alerts with total orange book data alerts. When there is a match, compare its row cell values for each and every column and if there is difference the code should be written as Update in sheet orange book data alerts column N and if the unique value is not available it should write New addition in column N.. Below is code which i tried. it is working for if there is any update in data for one column, i need help so that i can compare all the columns and write as new addition if unique value is not present.

Sub compare10() 
Dim w1 As Worksheet, w2 As Worksheet 
Dim c As Range, a As Range 


Set w1 = Sheets("total orange book data") 
Set w2 = Sheets("orange book data alerts") 
With w1 
    For Each c In .Range("a2", .Range("a" & Rows.Count).End(xlUp)) 
        Set a = w2.Columns(1).Find(c.Value, lookat:=xlWhole) 
        If Not a is nothing then
            if.cells(c.row,10).value<>w2.cells(a.row(a.row,10) then

            w2.Cells(a.Row, 14).Value = "update" 
        End If 
    Next c 
End With


End Sub

You need to add the worksheet you expect the Rows.Count to run on. I assume it'll be .Range("a" & .Rows.Count).End(xlUp). Also, what's If.Cells(c.Row,10).... ? I assume a typo when pasting to SO? — BruceWayne
– BruceWayne, Commented Aug 31, 2016 at 17:39
you mean to say For Each c In .Range("a2", .Range("a" & Rows.Count).End(xlUp)) should be .For Each c In Range("a" & .Rows.Count).End(xlUp) stackoverflow.com/users/6701192/partyhatpanda stackoverflow.com/users/4650297/brucewayne — manoj prabhakar
– manoj prabhakar, Commented Aug 31, 2016 at 17:43

Arun Thomas · Accepted Answer · 2016-08-31 18:07:41Z

1

Sorry to suggest an entirely new code to you, but the below code will do the job [this is the way i do it, tested and working]

Sub compare10()

Dim found As Integer

Worksheets("orange book data alerts").Activate
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Worksheets("total orange book data alerts").Activate
With ActiveSheet
lastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Worksheets("orange book data alerts").Activate

For i = 1 To lastRow
    found = 0
    For j = 1 To lastRow2
        If Sheets("orange book data alerts").Cells(i, 1).Value = Sheets("total orange book data alerts").Cells(j, 1).Value Then
            found = 1
            For k = 1 To 13
                If Sheets("orange book data alerts").Cells(i, k).Value <> Sheets("total orange book data alerts").Cells(j, k).Value Then
                    Sheets("orange book data alerts").Cells(i, 14).Value = "Update"
                End If
            Next k
        End If
    Next j
    If found = 0 Then
        Sheets("orange book data alerts").Cells(i, 14).Value = "New Addition"
    End If
Next i

End Sub

edited Aug 31, 2016 at 18:07

answered Aug 31, 2016 at 17:49

Arun Thomas

8751 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

manoj prabhakar Over a year ago

thank you arun you code looks pretty good, but my number of rows gets changed in both sheets when i run daily how to make it dynamic? stackoverflow.com/users/6400030/arun-thomas

Arun Thomas Over a year ago

see my updated answer, this will find the number of rows of data in each sheet and analyse accordingly

manoj prabhakar Over a year ago

Thank you so much Thomas. I have a question is there an way in vba to pull data if last 15days from current day. Present iam using lookup function to match and deleting unmatched dates. Once again thank you for instant help. stackoverflow.com/users/6400030/arun-thomas

Arun Thomas Over a year ago

if you have the date to be checked in cell A1, then you could use the condition If Day(Cells(1, 1).Value) > Day(today) - 15 Then. And it would be much appreciated if you could accept my answer if it helped and this marks the solution to others who visits

manoj prabhakar Over a year ago

Thomas your code is working fine for the current day date it is not deleting the row if it is = today date, and it is deleting the rows having dates before today. My question how the macro can pull the data of last 15days from current day.stackoverflow.com/users/6400030/arun-thomas

|

Collectives™ on Stack Overflow

excel vba comparing two sheets based on unique value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related