Compare 2 worksheets for difference

[hecgroups]

Hello Again,

I am looking for VBA macros to compare 2 worksheets within same workbook. I am always receiving more than 10,000 to 50,000 member’s data on daily basis.

Compare worksheet (Quote List) with worksheet (Policy Issuance List) based on ID number in column H. if difference found then copy entire row to worksheet (Discrepancy Records) and highlight cell value which is changed.

For Example:

In worksheet (Quote List) look for ID number “2099421261” i.e. row no. 1 in worksheet (Policy Issuance List) i.e. row no. 3 and found difference in age. So, copy the entire row to worksheet (Discrepancy Records) and highlight cell value E2 in worksheet (Discrepancy Records).

I hope that above details are sufficient to understand my problem.

Copy of a sample worksheet is attached for your reference.

[stnkynts]

Please Login or Register  to view this content.

[hecgroups]

Thanks for your response.

Now the issue is it is not extracting new records which is in worksheet (Policy Issuance List).

It is highlighting those cell values which is identical means same values in both sheets.

For example:

ASEME ATIK records which is having same data but it is extracting as a error record.
Philip Atik record their is difference in Gender which mM and M

Kindly see the attachment in my first post to get a idea what will be the output.

[stanleydgromjr]

Cross posted here:

http://www.mrexcel.com/forum/excel-q...ml#post4164934

A message to forum cross posters.

Please read this:
http://www.excelguru.ca/node/7

[hecgroups]

Thank Stan for your notification.

I have already post my original link on the other form.

[hecgroups]

can anyone help me out please

[hecgroups]

can anyone help me please

[stnkynts]

Is it common for an ID number to have "**"? This acts as a wild card and messes with the code. I can account for this but need to know if the "**" actually shows up in your real data.

[hecgroups]

No? id cards will be numbers only. I have put these for testing only. I have attached a sample file how will be the output.

Thanks in advance for your help

[stnkynts]

Cell H8 of the sheet "Quote List" has "**" as I indicated. If you change that to an actual number, on both the Quote List sheet and the Policy Issuance List sheet, the code provides the report exactly as you requested:

For example:

ASEME ATIK records which is having same data but it is extracting as a error record.
Philip Atik record their is difference in Gender which mM and M

Since your actual data will not have "**" in it, the original code should be sufficient.

[hecgroups]

Sorry I have just check thoroughly all the possibilities of comparing data and it working but the only issue is mentioned below.

1. It is not finding newly record which is not exists in the "Quote List" worksheet and present in "Policy Issuance List" worksheet.
2. It is not finding removed record which is exist in the "Quote List " worksheet and not present in"Policy Issuance List" worksheet.
3. It is not returning the remarks.

Note: If their is not difference found it should popup a message saying "No difference found"

Please net me know if you need any sample attachment. But I think the attachment in first post is sufficient.

[stnkynts]

1. There is no parameter to be able to determine if a record is "new". It simply has no differences.
2. Amended
3. It is copying remarks. It is just copying remarks on the Policy Issuance Sheet. If your remarks are only on the Quote List sheet there was no way for me to know that since your example workbook did not show any remarks on either sheet. It has been amended to report the Quote List sheet values instead of Policy Issuance Sheet

Please Login or Register  to view this content.

[hecgroups]

Thanks stnkynts. However I have do this manual for add/remove record.

Thanks for your help. Topic marked as solved and reputation added.