Link to home
Start Free TrialLog in
Avatar of krishnaswamy velayudham
krishnaswamy velayudham

asked on

Comparison of Values between two sheets

I initially had the code to do the following :
Compare the ranges between between sheet1 and sheet2 , when they have unique values and they are below the specified date ( March 25th , 2019 ) , the record should get deleted in sheet1 and when it above the specified date then the record should get copied below the last row of sheet1.
Option Explicit

Sub Operation()
    Dim Sheet1 As Excel.Worksheet
    Set Sheet1 = ThisWorkbook.Sheets("Sheet1")
    
    Dim Sheet2 As Excel.Worksheet
    Set Sheet2 = ThisWorkbook.Sheets("Sheet2")
    
    Dim SpdDate As Date
    SpdDate = DateSerial(2019, 3, 25)    '// DateValue("March 25,2019")
    
    DeleteOldTasks Sheet1, SpdDate, Sheet2
    InsertNewTasks Sheet2, Sheet1
End Sub

Private Sub DeleteOldTasks(ByRef Target As Excel.Worksheet, ByVal dt As Date, ByRef Source As Excel.Worksheet)
    Dim LastRow As Long
    LastRow = Target.Cells(Target.Rows.Count, 2).End(xlUp).Row
    
    Dim i As Long
    For i = LastRow To 2 Step -1
        Dim rng As Excel.Range
        Set rng = Target.Range("B" & i & ":C" & i)

        If (rng.Cells(2) < dt) Then
            If Not (TaskDateExist(Source, rng)) Then
                rng.Cells(2).EntireRow.Delete
            End If
        End If
    Next
End Sub

Private Function TaskDateExist(ByRef Source As Excel.Worksheet, ByRef rng As Excel.Range)
    Dim LastRow As Long
    LastRow = Source.Cells(Source.Rows.Count, 2).End(xlUp).Row
    
    Dim i As Long
    For i = 2 To LastRow
        Dim lookupRng As Excel.Range
        Set lookupRng = Source.Range("B" & i & ":C" & i)
        
        If ((rng.Cells(1).Value = lookupRng.Cells(1).Value) And _
           (rng.Cells(2).Value = lookupRng.Cells(2).Value)) Then
            TaskDateExist = True
        End If
    Next
End Function
Private Sub InsertNewTasks(ByRef Source As Excel.Worksheet, ByRef Target As Excel.Worksheet)
    Dim LastRow As Long
    LastRow = Target.Cells(Target.Rows.Count, 2).End(xlUp).Row
    
    Dim LastDate As Date
    LastDate = Target.Range("C" & LastRow)
    
    Dim RowToInsert As Excel.Range
    Set RowToInsert = Target.Range("A" & LastRow & ":" & "C" & LastRow).Offset(RowOffset:=1)
    
    LastRow = Source.Cells(Source.Rows.Count, 2).End(xlUp).Row
    
    Dim i As Long
    For i = 2 To LastRow
        If (Source.Range("C" & i) > LastDate) Then
            RowToInsert.Cells(1) = Source.Range("A" & i)
            RowToInsert.Cells(2) = Source.Range("B" & i)
            RowToInsert.Cells(3) = Source.Range("C" & i)
        End If
    Next
End Sub

Open in new window


Simple Version of Worksheet
Sheet1
    order   task    date
     1       abb    3/24/2019
     1       bcc    3/24/2019
     2       ccd    3/25/2019
     3       dde    3/26/2019

Open in new window


Sheet2

    order   task    date
     1       bcc    3/24/2019
     2       ccd    3/25/2019
     3       eff    3/27/2019

Open in new window


Final Result

   
 order   task    date
     1       bcc    3/24/2019
     2       ccd    3/25/2019
     3       dde    3/26/2019
     3       eff    3/27/2019 

Open in new window



Now I like to implement them in bigger worksheet with many columns . When I execute , I get errors . Could someone point out the errors.

I want  to edit the following  the code for a comparing the range of values in a record ( D6:G ) IN sheet1 to the range of values in record ( B2:E ) IN sheet2 .Each record has dates on column ( K6 :K in sheet1 & I2:I in sheet2 ) . I need to compare records with dates also . When the unique records are found below specified date ( 3/10/2019) , the entire row of the records should get deleted in sheet2 and unique records above the specified date should be copied from sheet1 to sheet2.

I have code for comparing records between sheets ,  when I edited them it does not run as needed to . I like someone to give ur valuable guidance

Option Explicit
Sub Operation()
    Dim Sheet1 As Excel.Worksheet
    Set Sheet1 = ThisWorkbook.Sheets("Sheet1")
    
    Dim Sheet2 As Excel.Worksheet
    Set Sheet2 = ThisWorkbook.Sheets("Sheet2")
    
    Dim SpdDate As Date
    SpdDate = DateSerial(2019, 3, 10)    '// DateValue("March 10,2019")
    
    DeleteOldTasks Sheet1, SpdDate, Sheet2
    InsertNewTasks Sheet2, Sheet1
End Sub

Private Sub DeleteOldTasks(ByRef target As Excel.Worksheet, ByVal dt As Date, ByRef source As Excel.Worksheet)
    Dim LastRow As Long
    LastRow = target.Cells(target.Rows.Count, 2).End(xlUp).Row
    
    Dim i As Long
    For i = LastRow To 2 Step -1
        Dim rng As Excel.Range
        Set rng = target.Range("B2" & i & ":E" & i)

        If (rng.Cells(2) < dt) Then
            If Not (TaskDateExist(source, rng)) Then
                rng.Cells(2).EntireRow.Delete
            End If
        End If
    Next
End Sub

Private Function TaskDateExist(ByRef source As Excel.Worksheet, ByRef rng As Excel.Range)
    Dim LastRow As Long
    LastRow = source.Cells(source.Rows.Count, 2).End(xlUp).Row
    
    Dim i As Long
    For i = 2 To LastRow
        Dim lookupRng As Excel.Range
        Set lookupRng = source.Range("D6" & i & ":G" & i)
        
        If ((rng.Cells(1).Value = lookupRng.Cells(1).Value) And _
           (rng.Cells(2).Value = lookupRng.Cells(2).Value)) Then
            TaskDateExist = True
        End If
    Next
End Function

Private Sub InsertNewTasks(ByRef Source As Excel.Worksheet, ByRef Target As Excel.Worksheet)
    Dim LastRow As Long
    LastRow = Target.Cells(Target.Rows.Count, 2).End(xlUp).Row
    
    Dim LastDate As Date
    LastDate = Target.Range("C" & LastRow)
    
    Dim RowToInsert As Excel.Range
    Set RowToInsert = Target.Range("A" & LastRow & ":" & "L" & LastRow).Offset(RowOffset:=1)
    
    LastRow = Source.Cells(Source.Rows.Count, 2).End(xlUp).Row
    
    Dim i As Long
    For i = 2 To LastRow
        If (Source.Range("C" & i) > LastDate) Then
            RowToInsert.Cells(1) = Source.Range("A" & i)
            RowToInsert.Cells(2) = Source.Range("B" & i)
            RowToInsert.Cells(3) = Source.Range("C" & i)
           RowToInsert.Cells(4) = Source.Range("D" & i)
            RowToInsert.Cells(5) = Source.Range("E" & i)
            RowToInsert.Cells(6) = Source.Range("F" & i)
            RowToInsert.Cells(7) = Source.Range("G" & i)
            RowToInsert.Cells(8) = Source.Range("H" & i)
            RowToInsert.Cells(9) = Source.Range("I" & i)
            RowToInsert.Cells(10) = Source.Range("J" & i)
            RowToInsert.Cells(11) = Source.Range("K" & i)
            RowToInsert.Cells(12) = Source.Range("L" & i)
   
        End If
    Next
End Sub

Open in new window


I have initially created the code for simple worksheet below :

User generated imageUser generated imageUser generated image
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Please attach the simple workbook.
Well said Martin !!!
Avatar of krishnaswamy velayudham
krishnaswamy velayudham

ASKER

Hi ,

      I have attached the worksheet .
Intial.xlsm
IT seems the road is long …. please post the long worksheet where you have a problem !!! we cannot start creating a long worksheet and testing
gowflow
First question where you want final result to display in Sheet1 or Sheet2 ?

Can you explain a bit on the work process as I feel there is some redundance.
gowflow
Any luck for the long worksheet ?
gowflow
Sorry . I was getting the file with new records for making it Easier . I like to compare the ranges B to E  in sheet
" Old" and with the other sheet " New" range D to G . The dates should be in Column Name " Finish Date" . Old sheet should get updated from the new one.
Sample.xlsm
ok first question:
Does Sheet Old have always data from Row 5 and from Col C ?? this is a bit akward given that New is from Col A row 1

ALso you said:
I like to compare the ranges B to E  in sheet
" Old" and with the other sheet " New" range D to G

From the file you posted I think you meant just the opposite !!! B to E in New to D to G in Old

Correct ??
gowflow
Also in your original post you have different layout as the one you just posted !!!! Sorry this is a total waste of time. You need to get your samples well structured and ready to have people helping you.

The date you refer to in your Initial post is Final Date ?? is this the date we need to check ??

Where is the checking date ? how are we suppose to check the Start Date ?
gowflow
Hi GowLow,

                      I know it seems bit weird that  the row starts at C5 because . I have many headings written on the space above . I have not written them here. Actually the ranges are specified are correct . I have written the worksheet name . I have edited the names and uploaded them here.
Sample.xlsm
Yes We to check the final date. The date must equal and less than 2/25/2019
Yes We to check the final date. The date must equal and less than 2/25/2019

OK now this date is 2/25/2019 what about later next week next month etc.... when you run the macro again the date will change right ? where do we take this date form ? Cannot change code every time you run it it is not professional

gowflow
Yup . I know , it does not look professional . I had an idea of creating a input box where it will enable the user to select the date based on their requirement  . I don't know whether , it can achieved . This was my idea  , if it can be implemented , it will be great.
You ask I do !!!

We can do input box or we can have a Cell Like that will hold this info especially that you have empty rows on top we can use an empty cell and put a title Date Start or whatever and the cell below it will be this date so when we run the macro it looks to this date. Let me do it for you.

I need first to be clear

We have this date lets say Date D
We look in both sheet for records old and New what you want with this date please explain it here.

Thanks
gowflow
When we click the the compare . We will  get the date from the user through input box . Then we compare the ranges with both the sheets . Once we get the unique values they must be checked with the dates in final date column  in "old" sheet  . When the dates which are equal or less than the dates specified  by  the user  then entire the records corresponding to the unique values should be deleted from the sheet "Old" and if the dates are more than the specified dates it must added to old sheet . Every Action should take place in the old sheet only.
ok so let me get this correctly.

OLD sheet is your cumulative sheet it is always updated so it has previous data and new one.

To update this sheet we have a new sheet in which there are records obviously much less than old (old I imagine will have 1000 record where new could only have 10 or more …

So we look in sheet NEW we take first record and look if this record exist in sheet old

if we find it and it match exactly then we look at the final date in old if the date in old is older or equal to the date specified by the user we delete this record from OLD

Is this correct ?

If the answer is yes then here are my question:
1) What do we do with the record from NEW in this case we copy it to OLD ?
2) If the date is newer than date specified by user we keep the record ?

let me know
gowflow
Yeah . Let me get this more clear .
Initially we compare the records from the new sheet to old sheet . When the records do match then look at the dates . When the dates are below the specified date . Then the entire  records are deleted in old sheet . The records which  have dates greater than the specified dates are copied from new sheet and get pasted in the old sheet.  
Old Sheet is the cumulative sheet that contains all the record new and old one.
very clear !
just one thing when you say paste the record from NEW to OLD it is ok to put it at the end ? I mean after last row ?
gowflow
Hi ,

        I made  a mistake in the previous comment . I have  explained them below.
This how the code should work
Initially we compare the records in new sheet with the old sheet .

There will be  records in old sheet  which do not have a matching records in New Sheet . These records should be then checked with the final date column , if it below the specified date , it should be deleted . If it above the specified date it should be kept without any changes .

There will be records in the new sheet which do not have matching records with the old sheet should then these records should be compared  the specified date with the dates in final date column . If it is above the specified date , it should get copied and get pasted below last row in the old sheet . If it below the specified date . Keep the record without any changes.
You got me CONFUSED !!!

The before Last post you talk about MATCHING records … and what to do with them
The Last Post you talk about NON MATCHING records … and what to do with them

SO WHAT IS THE STORY ?

We need to act on the Post or only the last one ?

Here is my suggestion:

1) I need first of all more data than what you have submitted as this is not enough to understand what you need and build a successful macro.
2) On the data that you will supply for the first 10 rows of each sheet you write at the last empty column what should the macro do for that specific row explaining why and so on for say 10 to 15 rows on OLD and also on as much as you can on sheet NEW.

This way I can have sample data and know what should be the outcome
gowflow
Hi Gowflow ,

                        I am really sorry . If I have confused you .  I admit the way , I said was confusing  . Let I tell everything I need from the code. This one is Final.

We have two sheets "old" and "New" . "Old" is the permanent one and "New" is the temporary one . We will be comparing the new sheet with the old sheet . The goal of the application is to delete the older tasks in the "old" sheet and newer task must be created at the bottom of the sheet from the "New" Sheet.

When we click the Update Button , we will ask the user for Date . Then we compare the "New" Sheet with the "Old" sheet for the ranges I have specified .( B2:E500  in "Old" Sheet and D6:G500 in "New"  Sheet ) . There will records which will not have any matching in both sheets . These records should should be then compared with the user specified date and dates in "Final Dates " Column  the sheets . If the dates are below the specified date , the entire row of the records should be deleted .

As most of the Newer records are found in "New" sheet . There will be records which are not present in the "Old"  sheet . These records will be checked with user specified date with the "final date" column . If it is above them , it will eventually copied and pasted below the last row in "old" sheet .

The Matching Records will be left unchanged . We should be working with records which do not have a match .
We can continue with the range I have mentioned , It won't go beyond them.
ok all this.

I need FACTS

Please do what I asked. otherwhysere sorry not ready to put time on this as already too much wasted time.

Get more data and when I say more data I expect 100 rows. ...
write at the end of every row what should happen (delete move to old keep … and why coz date older cos not matching ….

Less than this SORRY cannot help

gowflow
Yeah . Give me few minutes . I will upload them.
No problem take ur time it is here 2:30 AM need to get some sleep will look at this tomorrow
gowflow
Hi Gowflow ,

                        I have named the rows to be added and to removed in the attached worksheet
Sample.xlsm
Well Please indicate for each one why No change and why to be removed and why to be added.

As looking at this I spent 1/2 hour and could not get any sense from what you flaged.

For example for this Job in Old503970      70      Bottom Panel Assembly
503970      80      Door Assembly
503970      90      Frame/Plate Assembly
503970      110      Quality Inspection
503970      120      Packing

You flagged only the first item to be removed the rest is no change
bearing in mind that in New you have
503970      70      Bottom Panel Assembly
503970      80      Door Assembly
503970      90      Frame/Plate Assembly
503970      110      Quality Inspection
503970      120      Packing
All showing No change and date in both is Feb 26

WHAT IS THE SECRET ????

I think the road is going to be very long with you
gowflow
Hi Gowflow ,

                       I am extremely sorry . To make things easier for you . I am making them difficult day by day . I have deleted the particular task 503970      70      Bottom Panel Assembly in new sheet . Now It will make sense.

Thanks
Sample--1-.xlsm
I hope  , now it will easier for you to understand
If you only deleted a row and did not bother to say why the action then sorry cannot help u further.

If u did its fine i ll look at it once i am base coz driving

Wanted to get the message accross
Hi Gowflow ,

                        The main reason for the application is to delete the older Operations present in the "old" sheet and add newer Operations  in "Old" Sheet .  Whenever a "New " sheet comes , it indicates the operations which are currently in progress where there will few  operations  removed for some Order  indicating these are finished  and newer operations added for new orders .

I wanted to delete the operations for orders which not found in "New" Sheet and Add newer operations for orders which are found  in "New" Sheet .  

Order Number are indicated by "Job" Column and the Operations are indicated in "Opr" and "OpDesc".  

As we are both adding and deleting the operations at the same time . We need some kind of filter which will help us to get  it done . Hence I thought of getting the date from the user .
 
When a new sheet arrives ,  there will operations for orders which are both new and old .  When we Compare the two sheets , there will be some operations which will not match with the "Old" Sheet for orders . Every  record will have a final date . Hence operations which do not have a match in the "old" sheet , lesser then the specified date will be removed and above the specified date will be added .

If you have a better way of doing that , you are welcome to give your opinion.  I just want to delete older operations for some orders and add operations for new orders in "old" sheet.
1) When an order is still under process ie not finished I presume it will show 'completely' in a sheet NEW is that correct ?
2) When an order has finished completely and no more activity on it should not appear in sheet NEW is that correct ?

if both assumptions 1 and 2 are to be correct then I propose very simply the following:

Every time we get the New sheet the macro will do the following
1) Macro will sort the New sheet to make sure it is sorted by Job and by Finished date.
2) Macro will take first order or Job (could be several rows .. it doesn't matter) it will look for that Job in sheet OLD whatever is found whether 1 row several rows for that job all to be REMOVED and the job from New to be added to sheet OLD.

That's it that's all.

This way you will always have in Sheet OLD ALL THE DATA up to last Update.

Please let me know your thoughts
gowflow
Yeah . We are on the same page . We can use the Job and Operation as Combo for comparing  with "Old" sheet and "New" Sheet. If it makes sense.
You have not answered my logic !!!

Again looking at the sample you provided I do not understand why you want to remove
503971      70      Bottom Panel Assembly
503971      80      Door Assembly
503971      90      Frame/Plate Assembly
503971      110      Quality Inspection
503971      120      Packing

From OLD !!!! Simply because it does not exist in NEW ?? If yes then there are also other Jobs in OLD that do not exist in new and you flagged as no change !!!

Answer if my logic is correct
gowflow
What is the objective of having the sheet OLD ???
To keep all ORDERS ?? that have the latest operations ? Or only ORDERS that are not yet finished ???

I am totally confused as to your system.

I think the problem is you are too much focused on the small details that you forgot to explain why you need the sheets and what they should contain.

gowflow
Hi ,

       Your logic was correct .When the records not exist in new sheet delete them  in the old sheet . newer records in New sheet will be added to old sheet .  

Every time we get the New sheet the macro will do the following
1) Macro will sort the New sheet to make sure it is sorted by Job and by Finished date. -         Yeah it Looks good
2) Macro will take first order or Job (could be several rows .. it doesn't matter) it will look for that Job in sheet OLD whatever is found whether 1 row several rows for that job all to be REMOVED and the job from New to be added to sheet OLD.  - I suggest we take as range from Job to OpDesc to compare , Keep all the rows that match and delete the ones which do not have a match  . When the records do not have match delete them .


I wanted to delete these records because , it doesn't exist in new sheet . If I left anything , then it may also included.
 
503983      80      Door Assembly
503983      90      Frame/Plate Assembly
503970      70      Bottom Panel Assembly
503971      70      Bottom Panel Assembly
503971      80      Door Assembly
503971      90      Frame/Plate Assembly
503971      110      Quality Inspection
503971      120      Packing
the objective of the old sheet is to get all active operations.
Newer Operations Found in New Sheet will be added .  To make it more simple , Delete all records in Old sheet which is not in New Sheet and Add records to Old which are Found in New Sheet
I give up !!

Sorry I never said
Your logic was correct .[When the records not exist in new sheet delete them  in the old sheet ]

What I proposed to do is look for all the orders in sheet NEW !!!! so I am only looking for all records in sheet new and then comparing with OLD for the records found.

I never said I will also look for records in sheet OLD to see if they exist in sheet NEW then this is chaos and I do not understand

gowflow
Sir ,  One last chance . You do your code . I will then see how it works .
Once I get them , I will  look at them further and make the changes  .
So at the end this is what you want:

Newer Operations Found in New Sheet will be added .  To make it more simple , Delete all records in Old sheet which is not in New Sheet and Add records to Old which are Found in New Sheet


to summarise
1) Run through all records in OLD
      if found in new  ---> Do nothing
      if not found in NEW ----> Deleted from OLD.

2) Run through all records in NEW
      if found in OLD ---> Do nothing
      if not found in OLD ----> Add it to NEW

Is this what you want ????
gowflow
SIR !!!! I am not an idiot here I cannot make a code that I have no clue what it is suppose to do !!!
You are talking to a human not a machine

You are making me crazy
gowflow
I am getting confused with our conversation . Once we develop initial code . I can develop them or ask for help in altering  . You create codes that look in New Sheet  with the Old Sheet . Lets go from there . I am working on my thesis . I am struck here , Your help will be useful .
to summarise
1) Run through all records in OLD
      if found in new  ---> Do nothing
      if not found in NEW ----> Deleted from OLD.

2) Run through all records in NEW
      if found in OLD ---> Do nothing
      if not found in OLD ----> Add it to NEW

That's it
Sorry this is the first time since 2009 that I have to say NO !!!

Have a nice day
gowflow
Sir ,  

       Could you do the first part  , It will do
1) Run through all records in OLD
      if found in new  ---> Do nothing
      if not found in NEW ----> Deleted from OLD.
It will work .
Hi Gowflow ,

                        Can you do it  ? .  Your help will be very useful . Please don't give up .

Thanks
It is my pleasure to do !!! I never say no but I hate not to hit it 100% coz you have been so much on and off that I am totally confused.

If what I pointed is exactly the solution I can get it 100% but I hate to do a job and have you redo or change coz you idd not take the time to well think what you want.

This is the feeling you are leaving me with.

So before I say yes

PLEASE and for god sake read well the below and make sure it is exactly what you want add to it subtract but let it be final then I have no problem.


1) Run through all records in OLD
      if found in new  ---> Do nothing
      if not found in NEW ----> Deleted from OLD.

2) Run through all records in NEW
      if found in OLD ---> Do nothing
      if not found in OLD ----> Add it to NEW


Let me know.
gowflow
Sir ,

        That's What I want . You got it . You have summarized it .
1) Run through all records in OLD
      if found in new  ---> Do nothing
      if not found in NEW ----> Deleted from OLD.

2) Run through all records in NEW
      if found in OLD ---> Do nothing
      if not found in OLD ----> Add it to NEW

We can do two function individually . One  for Deleting  and One for Adding  . If it makes them work faster .
Hi Gowflow ,

                  Are u still on board .
Why you changed the question to reflect what I proposed ????

It shows as if I do not understand what you want !!! This is getting absurd

gowflow
It summarizes all . So I changed them
It will not confuse anyone anymore . That's what I am looking to achieve
Sorry if someone is reading the question and see my comments trying to understand … he will say this guy doesn't understand anything the question is clear !!!!
Yes but it is turning WRITER (ie myself) into an idiot !!!

gowflow
I sorry , If I have confused you so much . I think finally we are able to come to a conclusion of what we need .
I will change the question back to original . I changed them mainly because It will help the people in future looking for solutions to problems similar to mine.
Can you work on the code .
Hi Gowflow,
         
                          Are u still working with me on this
ok will do just after midnite here will attend asap
gowflow
Thank you very much
Hi Gowflow ,

                      Did u get it ?
ok here you are. This workbook contain the code in a new module called Update. I added a new module called OwnerModule in which I placed all your previous code that was in ThisWorkbook. As a general rule you should not have independent Sub in ThisWorkbook but rather either in the sheet itself or in a Module. ThisWorkbook should be reserved to have code only pertaining to the events of this Workbook on Open on Activate per sheet for all the sheets etc...

To go back to the Macroes.

I created a General Macro Update that will launch 2 separate Macroes
UpdateNEW
UpdateOLD

What I did is I wrote the final Macroes as agreed in the last comment and on the last line or portion of every sub I disabled the Add to OLD and Delete to OLD (the instructions are there but commented out) I instead Updated the 2 columns in Yellow with the result of the Macro.

Please Enable Macroes, then Press on the button Update and once done check the results in both Columns. You will see that the result is different from what you have pointed out at some records.

Here is the Code that concenrs all the Macro I built.

Option Explicit

Sub UpdateNEW()
'   This Sub will do the Following Update
'   Run through all records in NEW
'      if found in OLD ---> Do nothing
'      if not found in OLD ----> Add it to OLD
'

Dim WSO As Worksheet
Dim WSN As Worksheet
Dim MaxRowO As Long, MaxRowN As Long, I As Long, J As Long
Dim sJob As String, sOps As String, sFirstAddress As String
Dim cCell As Range
Dim bNotFound As Boolean


'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'---> Set Variables
Set WSO = Sheets("Old")
Set WSN = Sheets("New")
MaxRowO = WSO.Range("A" & WSO.Rows.Count).End(xlUp).Row
MaxRowN = WSN.Range("C" & WSN.Rows.Count).End(xlUp).Row
WSN.Range("P6:P" & MaxRowN).ClearContents

'---> Loop thruough all rows in sheet New
For I = 6 To MaxRowN
    bNotFound = False
    sJob = WSN.Cells(I, "D")
    sOps = WSN.Cells(I, "E")
    Set cCell = WSO.Range("B2:B" & MaxRowO).Find(what:=sJob, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not cCell Is Nothing Then
        bNotFound = True
        sFirstAddress = cCell.Address
        Do
            '---> Check to See if Ops if found for that Job
            If WSO.Cells(cCell.Row, "C") = sOps Then
                bNotFound = False
                Exit Do
            End If
            Set cCell = WSO.Range("B2:C" & MaxRowO).FindNext(cCell)
        Loop While Not cCell Is Nothing And cCell.Address <> sFirstAddress
    Else
        bNotFound = True
    End If
    
    '---> Add Record to OLD if Not Found
    If bNotFound Then
        'WSN.Range("C" & I & ":O" & I).Copy WSO.Range("A" & MaxRowO + 1)
        WSN.Range("P" & I) = "Copied to OLD"
        WSO.Range("N" & MaxRowO + 1) = sJob & " " & sOps & " Copied from New row " & I
        MaxRowO = MaxRowO + 1
    End If
    
    
Next I

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With



End Sub

Sub UpdateOLD()
'     This Sub will do the Following Update
'     Run through all records in OLD
'        if found in NEW  ---> Do nothing
'        if not found in NEW ----> Delete it from OLD.
'
Dim WSO As Worksheet
Dim WSN As Worksheet
Dim MaxRowO As Long, MaxRowN As Long, I As Long, J As Long
Dim sJob As String, sOps As String, sFirstAddress As String
Dim cCell As Range
Dim bNotFound As Boolean


'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'---> Set Variables
Set WSO = Sheets("Old")
Set WSN = Sheets("New")
MaxRowO = WSO.Range("A" & WSO.Rows.Count).End(xlUp).Row
MaxRowN = WSN.Range("C" & WSN.Rows.Count).End(xlUp).Row
WSO.Range("N2:N" & MaxRowO).ClearContents

'---> Loop thruough all rows in sheet New
For I = MaxRowO To 2 Step -1
    bNotFound = False
    sJob = WSO.Cells(I, "B")
    sOps = WSO.Cells(I, "C")
    Set cCell = WSN.Range("D6:D" & MaxRowN).Find(what:=sJob, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not cCell Is Nothing Then
        bNotFound = True
        sFirstAddress = cCell.Address
        Do
            '---> Check to See if Ops if found for that Job
            If WSN.Cells(cCell.Row, "E") = sOps Then
                bNotFound = False
                Exit Do
            End If
            Set cCell = WSN.Range("D6:D" & MaxRowN).FindNext(cCell)
        Loop While Not cCell Is Nothing And cCell.Address <> sFirstAddress
    Else
        bNotFound = True
    End If
    
    '---> Del Record from OLD if Not Found
    If bNotFound Then
        'WSO.Range(I & ":" & I).EntireRow.Delete
        WSO.Range("N" & I) = sJob & " " & sOps & " Deleted as NOT found in NEW"
    End If
    
    
Next I

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With



End Sub

Sub Update()

If MsgBox("Are you ready to perform Update to OLD worksheet from worksheet NEW ?", vbQuestion + vbYesNo, "Update") = vbYes Then
    UpdateNEW
    UpdateOLD
    
    MsgBox "Update Completed, Please check Col P in sheet NEW and Col N in sheet OLD for results.", vbInformation, "Update"
Else
    MsgBox "Operation Canceled by User.", vbInformation, "Update Stoped"
End If

End Sub

Open in new window



Please check and advise
Regards
gowflow
Sample1-V01.xlsm
Nailed it . It is what I was looking for .
Could you remove the Macro Result Column indicating the operations done and Delete and just Add the rows as mentioned over there. It will be the final version.
If all the results are fine then yes I will remove the trace columns and perform the Add Delete. But Did you notice the first couple of records in OLD it mentioned that they should be deleted where you had them no change !!! Who is right you or the macro ?? Need to make sure before releasing the final version.

gowflow
Macro was right . I just checked each of the record .
I have mentioned it wrong . The Macro did the function . During deletion make sure that entire record gets deleted and when added entire record gets added.
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It's a pleasure working with you gowflow. You are  genius .
Thank you your welcome. Am glad we got to the bottom of it and to your taste. Do not hesitate to let me know if / when you need help with other issues.

Regards
Gowflow