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.
Simple Version of Worksheet
Sheet1
Sheet2
Final Result
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
I have initially created the code for simple worksheet below :
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
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
Sheet2
order task date
1 bcc 3/24/2019
2 ccd 3/25/2019
3 eff 3/27/2019
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
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
I have initially created the code for simple worksheet below :
Please attach the simple workbook.
Well said Martin !!!
ASKER
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
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
Can you explain a bit on the work process as I feel there is some redundance.
gowflow
Any luck for the long worksheet ?
gowflow
gowflow
ASKER
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
" 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:
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
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
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
ASKER
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
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
ASKER
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
ASKER
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
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
ASKER
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
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
ASKER
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.
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
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
ASKER
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.
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
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
ASKER
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.
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
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
ASKER
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
gowflow
ASKER
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
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
ASKER
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 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
ASKER
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
If u did its fine i ll look at it once i am base coz driving
Wanted to get the message accross
ASKER
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.
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
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
ASKER
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
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
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
ASKER
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
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
ASKER
the objective of the old sheet is to get all active operations.
ASKER
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
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
ASKER
Sir , One last chance . You do your code . I will then see how it works .
ASKER
Once I get them , I will look at them further and make the changes .
So at the end this is what you want:
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
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
You are talking to a human not a machine
You are making me crazy
gowflow
ASKER
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 .
ASKER
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
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
Have a nice day
gowflow
ASKER
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.
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.
ASKER
It will work .
ASKER
Hi Gowflow ,
Can you do it ? . Your help will be very useful . Please don't give up .
Thanks
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.
Let me know.
gowflow
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
ASKER
Sir ,
That's What I want . You got it . You have summarized it .
That's What I want . You got it . You have summarized it .
ASKER
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 .
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 .
ASKER
Hi Gowflow ,
Are u still on board .
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 shows as if I do not understand what you want !!! This is getting absurd
gowflow
ASKER
It summarizes all . So I changed them
ASKER
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
Yes but it is turning WRITER (ie myself) into an idiot !!!
gowflow
ASKER
I sorry , If I have confused you so much . I think finally we are able to come to a conclusion of what we need .
ASKER
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.
ASKER
Can you work on the code .
ASKER
Hi Gowflow,
Are u still working with me on this
Are u still working with me on this
ok will do just after midnite here will attend asap
gowflow
gowflow
ASKER
Thank you very much
ASKER
Hi Gowflow ,
Did u get it ?
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.
Please check and advise
Regards
gowflow
Sample1-V01.xlsm
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
Please check and advise
Regards
gowflow
Sample1-V01.xlsm
ASKER
Nailed it . It is what I was looking for .
ASKER
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
gowflow
ASKER
Macro was right . I just checked each of the record .
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Regards
Gowflow
ASKER
Sure