compare multiple worksheets for duplicates

[ellac]

Hi everyone, I am trying to post this message again.

I need to compare multiple worksheets and find duplicates. When the duplicates are found they need to automatically be copied to a new worksheet in the same workbook.

That worksheet will then be password protected.

Can anyone help me, I need to finish this today.

Thanks so much for all the input.

 

[clipro]

Hi ellac,

At first I am sorry but I do not know how to make it in excel but it is possible to do it in Access and it is easy so if it will be usefull to you to do this in access call back and I'll explain it to you

Regards!

 

[ellac]

Thanks very much, but I really need it to be in Excel.

Can anyone help?

Thanks All!!!

 

[chiello]

I'm not sure I've understood what you need.

1)
You have 2 Source Workbook, say A and B.
You have to compare each Worksheet of A with Worksheets of B.
If you find duplicates you have to copy them in a different Workbook, say C, which will be password protected.

2)
You have single source workbook, say A, with multiple worksheets to be compared. If duplicates are found out then copy this to destination Workbook, say B, which will be password protected.

Three questions
a) Is either 1) or 2) correct
b) What to do with duplicates after copy? (i.e. delete, etc..)
c) Two worksheets are duplicated whether have the same value cell by cell?

 

[ellac]

Number 2 is almost correct. It would be one source workbook with multiple sheets, but the duplicates would be copied to a new worksheet in the existing source workbook.

I did not understand your C) question.

Thanks

 

[clipro]

I think that it is possible with a query in excel but give us an example of your sheets

I saw your note "I need to finish this today" it is really very easy to do it in access and to export it in excel after that so your file will be in excel

but if you finaly preffer excel send an example sheet

 

[ellac]

It is hard to send an example beccause it is confidential info.

But lets look at it this way. Sheet 1, sheet 2 and sheet 3 has info in column A B and C. Some of the info. in these columns has been duplicated. If I run a macro, it should pull out the cells or rows that have been duplicated and copy them into sheet4.

I hope this makes more sense.

Thanks

 

[clipro]

Try this:

copy formula in sheet 4 in range A1:C(end of your table)
=IF(IF(ISERROR(VLOOKUP(Sheet1!A2;Sheet2!A:A;1;FALSE));"";(VLOOKUP(Sheet1!A2;Sheet2!A:A;1;FALSE)))="";IF(ISERROR(VLOOKUP(Sheet1!A2;Sheet3!A:A;1;FALSE));"";(VLOOKUP(Sheet1!A2;Sheet3!A:A;1;FALSE)));(IF(ISERROR(VLOOKUP(Sheet1!A2;Sheet2!A:A;1;FALSE));"";(VLOOKUP(Sheet1!A2;Sheet2!A:A;1;FALSE)))))

it shows the duplicates from sheet 1 in sheets 2 and 3

now you can protect the sheet

is the formula works exactly as you need?

 

[ellac]

It did not work at all.

Also, I don't know if it will do what I need?

Can we try again.

Thanks so much for all your time and effort.

 

[clipro]

what happends?

1.the formula does not work at all
2. the result is far away from expected one

please describe me the problem

btw when is the end of your worktime? in meaning how much time do we have?