compare data and move it to new sheet

Hi, I'm new to VBA. and can any one assist me to have vba cod that can do the following.


\1


1. I need to compare data by "Item No" in both Sheets " sheet1 and sheet2" and move any duplicated rows to a new sheet "Sheet3".
2 . in sheet three I need it to sort the data by "Item No"

I will be appreciate any assist.
Regards

Hi Vikas_Gautam.

thank you for your reply.
I'm getting 1004 error.

I don't what is the rong?

Sorry I am replaying late..

but you have to use helper columns..
first you have to list unique column ..
using following formula in A21..

Formula:

Please Login or Register  to view this content.

use ctrl + shift + enter to enter the formula..
and drag it down..
you will get a unique cell list...
after that..
count the occurrence of each of the unique cell...
by using following formula in B21..

Formula:

Please Login or Register  to view this content.

drag it down..

do this for both the sheet..

then use following code to move the duplicates..

Please Login or Register  to view this content.

Don't forget to click *

Hi Vikas_Gautam

Thank you very much.

I'm getting error with that formula.

I have upload the file can you pls add the formula to it.

Regards

Hi Savio,
Try the attached

Hi AB33

Thank you very much for your assistance you really help me a lots.

One things still needed.

I need to add new Column to show sheets name which the copy was made from.

Please see the attached file sheet3

Update code:

thank you very much Dear AB33

Hi ab33

How can I make read the sheet name as i name it, not sheet1 or sheet2 for ex: I named sheet1 to Items and i named sheet2 to Qu

Thanks again

sheet1 and sheet2 are in parenthesis on the code "", change them in what ever you like.
Y(k, j) = "Sheet2"

I have now updated the code. The sheet names for the entire code will be picked up from the 1st 3 top rows. All you have to do is change the names on the 3 lines. The code will do the rest.

Hi,

Could you please modify the code to create new sheet for duplicate cell automatically with the name "Items Duplicate"

All the best

Hi
any one can help