Compare Worksheets

[Trevor G]

I have been requested to reslove an issue for a department at work. The issue is:

2 worksheets have to be compared by checking 3 columns to see if there are any records which do not have the same Policy Reference and if the dates for inception and expiry are out side the Master Data sheet information then copy the records that fall outside to another sheet.

I have copied some of the data into a workbook, the first sheet is the sheet which holds the data to be checked and the second sheet is the master data. Both extracts are coming from 2 different databases.

Any help to resolve this would be appreciated

 

[smiler44]

Are you trying to compare Participant 1 ReferenceINCEPT DATEEXPIRY DATE with
Policy Reference 1InceptionExpiry
and if the date of incept and exipry on the check data sheet starts before or ends after the inception and expiry date on the master sheet you want to copy the record from the check data sheet onto a different sheet?
smiler44

 

[Trevor G]

Are you trying to compare Participant 1 ReferenceINCEPT DATEEXPIRY DATE with
Policy Reference 1InceptionExpiry
and if the date of incept and exipry on the check data sheet starts before or ends after the inception and expiry date on the master sheet you want to copy the record from the check data sheet onto a different sheet?
smiler44

Yes this is what I have been tasked to see if it can be done.

 

[Brianwarnock]

I didn't really understand this statement

if there are any records which do not have the same Policy Reference

however to do what smiler suggested is going to require two loops the outer stepping through the Master and the inner the Check sheet, after first sorting both in ascending order. On a match you do the date comparison check etc then take the next master and continue through the check sheet.

However a quick check with Vlookup shows only 35 hits so if this is a one off 2 vlookups returning the dates, a visual check and copy or not will probably be quicker and easier than coding.

Brian

 

[Trevor G]

Morning Brian,

I am not so firmiliar with your suggestion, would you be able to post back a working sample of your suggestion.

I have more knowledge in Access but the request is to use Excel.

Thnak you for participating in my thread.

 

[Brianwarnock]

Not sure when I will get time to write and test code but it did strike me later that with so little data a complete search each time is not likely to be a problem and this would simplify the code.

I have attached a workbook using the VLookup approach, having got the data I did a copy Pastespecial Values to sheet3 and then sorted ascending on Col F thus getting all the hits at the top , a simple visual check shows all of the dates on the Check sheet are equal to those on the master , do they qualify for keeping?

Again I will ask is this aone off , also will the amount of data increase if not.

Brian

 

[Brianwarnock]

When I get to tackle the code it will be based on 2 For next loops, something like, but I'm getting rusty.

For each Checkcell in Checkrange
  For each mastercell in master range
    If checkcell.value=mastercell.value Then
        This is where you check the dates using offset to get the data

eg If checkcell.offset(0,1)=mastercell.offset(0,1)  will compare the incept dates

if the requirement is met do the copy  checkcell.entirerow.copy I think and then Pastespecial after an insert and shift down I think
end if
next checkcell
   end if
next mastercell

Brian

 

[Brianwarnock]

Attached workbook with sample working code.

Brian

 

[Trevor G]

Good moring Brian,

Thank you for both versions and suggestions. I am sorry I never replied last night due to a family crisis.

I am looking at both options and will present both to the department and will move forward with the one they will select.

It will be the code that they will want to use though.

I am very grateful that you could spare the time to help me.

 

[Brianwarnock]

I've added a bit, making it more flexible ie it finds the last row in the sheets, shows how to add the headig row and a date/time of the run.

You may vary things of course adding more headers and renaming Sheet3

I also added a smiley face to the toolbar to run the macro, I don't know how the data is created in the workbook or if it is a new workbook every time.

Brian

 

[Trevor G]

Thank you Brian,

I will take a look at this, I have run it once but yet to look at the code.

I am grateful.