Compare two Excel Sheets which have Duplicate Values (Advanced)

Registration date
Monday October 10, 2016
Last seen
October 10, 2016

I'm trying to compare two excel sheets which have duplicate values in the matching column.

In both sheets the number of row count is different.

The value which I'm matching is repeated in both sheets. Once matched relevant column values need to be pulled out.

So far what I tried is VLOOKUP but it doesn't do well when comes to duplicate values.

I only want to solve the Duplicate value matching issue.

I tried to fix it functions but it seems not working. I do have a solution in mind but not able to get it on to a VB Code. So if someone can tell me how this can be done its highly appreciated.

Below is what I have in my mind

1. Count the number of times each value in Column A sheet1 appearing on sheet2
2. Let's say value 2 is appearing on Sheet1 for 5 times. And same appearing 3 times on sheet2.
3. Need to get the first occurrence of the value in sheet2 with the corresponding row number.
4. Lets say in Row 10 is the first occurence of the value2.
5. If so we need to have a loop to go from row number 10 to 12 (value appearing 3 times on sheet2)
6. in sheet one we have 5 occurrence of the value 2. for each occurrence we need to go from row 10 to row 12.
7. if any matches found in between, we need to extract the raw content, whole row to a new sheet.
share your thoughts.