Compare Price List

[hecgroups]

Hello Again,

I am looking for VBA Macros to compare 2 price list. I am trying to achieve the following.

1. Script look in sheet one i.e. Old list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
2. Script look in sheet two i.e. New list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
3. Script look in both the sheet for duplicate values it it is there then it should display in 3rd sheet i.e. duplicate

Also it should count the number of records on each sheet in column F1. If possible can we display the work % while checking both the lists.

Sample file is attached which will give you more clarity of my request.

[hecgroups]

Cam someone please help me

[hecgroups]

Anyone help me please

[watersev]

hi hecgroups, please check attachment, press "Find Duplicates" button

Notes:

The code does the following actions:
1. Looks for duplicates in Old List data. Duplicate is a simultaneous repeat of Service Code, Service Description and Price more than once
2. Looks for duplicates in New List data. Duplicate is simultaneous repeat of Service Code, Service Description and Price more than once
3. Looks for duplicates in both sheets provided duplicate means simultaneous repeat of Service Code, Service Description and Price exists more than once in the other sheet

For Old and New lists, the duplicate rows are separated by coma if multiple instances found. When comparing sheets and duplicate is found, the row number from Old list is followed by coma, row numbers (if multiple) are joined by &.

The code does not have % of completion feature (I treat as fancy so I never use it).

[hecgroups]

Thanks for your response for my request. I know that you will reply to me that is why i have send you PM. OK it is working as per my requirement on sample file. I will check on my original file and let you know.

I need same requirement for differences in both sheets. Can it be possible in this thread or i will request on new thread. What you suggest.

[hecgroups]

Hai Watersev i have just check on my original data the script is not executing. When i put the sample data it is working but not on original data. Can you check what is the problem is.

Sample file is attached for your revision.

Thanks for your usual cooperation.

[watersev]

3. Looks for duplicates in both sheets provided duplicate means simultaneous repeat of Service Code, Service Description and Price exists more than once in the other sheet

The code works exactly as described in post #4. To see what I mean please copy A2:C2 to A7:C7 on the New List and run the code.

[hecgroups]

I am sorry might be i did not explain to you properly.

In post # 1 3.Script look in both the sheet for duplicate values it it is there then it should display in 3rd sheet i.e. duplicate

It should look the duplicated in old sheet & new sheet for duplicate.

Option 1 & 2 is working fine the only issue is option 3.

the overall is i need to check the duplicate values in sheet itself and both the sheets.

I hope now you understand what i mean.

[watersev]

can I have the correct result then for the file in post #6? Because now it contradicts with the result of your initial file with result shown in post #1

[hecgroups]

Please see the sample done manually.

What i am trying to do here is the following:-

1. Looking the values is repeating within the same sheet i.e. old list.
2. Looking the values is repeating within the same sheet i.e. new list.
3. Looking the values is appear in both the sheets i.e. old list & new list.

I hope this is make clarification of the issue.

[watersev]

A12:F12 in which sheet

Duplicate

if it was an error, please check attachment

I've changed a bit how duplicate rows are represented after sheets comparison: rows from different sheets are separated by &

[watersev]

has A12:F12 been put in error as duplicate or I do not know something else?

[hecgroups]

A12:F12 in which sheet

[hecgroups]

I really appreciate your efforts thanks for your help. It is working as per my requirement.

I have another request. I need same thing but difference in 2 sheets.

I need to compare old list and new list for differences can you be possible.

[watersev]

as different thread with explanations, sample file and result - yes

[hecgroups]

Thanks for your efforts. Topic marked as solved and Reputation added.