• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare two sheets, add new records to master sheet, remove unmactched to historical sheet using vba

A

Abdulassees

New Member
Hello ,

I am looking for a specific problem to resolve.

1. I have two sheets which needs to be compare every week. One sheet is a master tracker Tab and another is DB1. The DB1's record may change every week. so I need to compare the Master Tracker with DB1. If any new addition in the DB1 record we need to add or e After comparing, the unmatched records in the sheet1 should move to Historical Tab

2. The new records should add to master tracker sheet.

3 I have attached a sample file for your reference

Thanks in advance
Azees
 

Attachments

  • Sample file for Compare Add and Delete.xlsx
    11.6 KB · Views: 12

Hi,

as a part is unclear (see red part in your initial post),
join an expecting result workbook according to your previous attachment …
​
 
Marc L said:
Hi,

as a part is unclear (see red part in your initial post),
join an expecting result workbook according to your previous attachment …
​
Click to expand...
Hi Mark,
Sorry for the unclear information.
As per the attached sheet,
1. I will delete all records in the DB1 tab every week and paste the data in the tab DB1 (the data is picking from different workbook). I wanted to compare the data in DB1 tab with MasterTracker Tab. The unique identifier is Column D ie. Co.ID
2. There are always chance that in the Tab DB1 , the records may change. ie there will be new Co. ID's or there may be Some Co. ID is missing.

3. If there are new Co. ID's in the Tab DB1, those records should automatically paste in the MasterTracker Tab. (Last row in the MasterTracker Tab)

4. If there were any Co. ID in the MasterTracker which is not available in the DB1 tab should moved to Historical Report.

I wanted to do this activity on every week. So that all the records in DB1 shuld match with MasterTracker Tab.

Thank you.
Azees.
 
Hi Nebu,
Thank you. Your attached sheet is working perfect. I will check in my actual excel file and get back in case of any challenges.
Thank you,
Azees.
 
Hi Nebu,
Thanks a lot, I have checked with the my actual excel file and it is working perfect. Thank you.
I have one more request in the same workbook.
1. Suppose, in the tab DB1 there is no change in the Co.ID, however Parent ID has changed, in that case need to show in the status column that "Parent Id Changed".

Thank again for your immediate response and solution.

Thanks,
Azees
 
Thank you Nebu. This is working. Thanks a lot.

The only problem I found here is that, when there are only one row in MasterTracker Tab, it will throw an error message. However I am thankful for the code, because I will have more than one record in my tabs.

Thanks a lot..
Regards,
Abdul
 
Hi Nebu,

Please find attached the working sheet. I have 2 records in MasterTracker and when I compare with the DB1 tab. I am getting an incorrect information in MasterTracker.
Problem 1. Few records are not copying from DB1 to MasterTracker
Problem 2. Few records are duplicating in MasterTracker
Kindly help me with the issue.

Thank you in advance for your great support.
Azees
 
Hi:

Find the attached.

Thanks
 

Attachments

  • Sample file for Compare Add and Delete.xlsm
    62.3 KB · Views: 5
Hi Nebu,

Thank you again. This is Awesome.. working superb.
I would really appreciate if you help me in one more solution. For the new Co.ID's , can I show in the status in the MasterTracker tab that "New Record Found".
Regards,
Azees
 
Hi:

In test macro give this line
Sheet1.Range("G" & Sheet1.Range("D" & Rows.Count).End(xlUp).Row) = "New Record Found"

After
Sheet4.Range("A" & fnd.Row & ":G" & fnd.Row).Copy Sheet1.Range("A" & Sheet1.Range("D" & Rows.Count).End(xlUp).Row + 1)

Thanks
 

Attachments

  • Sample file for Compare Add and Delete.xlsm
    62.6 KB · Views: 14
Hi Nebu,

Here I am facing one issue that, when there are more records in both sheets (MasterTracker and DB1). The system takes a lot time to process. Moreover it get stuck sometimes.
I have attached the sheet for your reference. Kindly help me.

1. compare with Co.ID in DB1 with Master Tracker. If Co.ID available in DB1 and not available in MasterTracker , then the macro should add Co.ID details to Master tracker.
2. If any Co.ID in DB1 which is not there in MasteTracker should move to HistoricalReport.

3. If the Co.ID same in DB1 and MasterTracker then it also check whether any change in parent ID/Manager name. If there is change in DB1 then it populate in the MAsaterTracker in Column G that ParentID/Manager name Changed.

with lots of expectation,
Azees
 

Attachments

  • TestFile.xlsm
    164.9 KB · Views: 2
Hi:

Find the attached. It is taking less than a minute to run in my machine.

Thanks
 

Attachments

  • TestFile.xlsm
    171.9 KB · Views: 7
Hi Nebu,
Thank you for the attachment. I am using excel 2010 and for me this take time 4-5 minutes or more.
1. Comparing with DB1 and MasterTracker and moving to Historical tab is working fine with me without much delay.

2. Searching Parent ID match and throwing result is taking time.. In the actual scenario , like Parent ID I need to check for any change in Manager name and Country name.

Can we have one module to search like this...
The Co.Id is unique in all the scinario
a) If Co.ID in MasterTracker = Co.ID in DB1 then check Parent ID in MasterTracker with DB1's Parent ID . If not matching say in Column G "Parent ID Changed"
a) If Co.ID in MasterTracker = Co.ID in DB1 then check Managername in MasterTracker with DB1's Managername . If not matching say in Column G "Manager Changed".
a) If Co.ID in MasterTracker = Co.ID in DB1 then check Countryname in MasterTracker with DB1's Countryname . If not matching say in Column G "Country Changed".
Regards,
Azees
 
Hi Nebu,
Please find the message box that I have received after running the macro. It is 608.62 seconds. Moreover the mastertracker gives duplicate files.

Regards,
Abdul
 
You must log in or register to reply here.
Back
Top