How to keep Duplicate Records

[Sam9]

I have excel file like below

in Sheet 1
A
10000
11000
17000
19000
21500



Sheet 2
A B
11000 RED
11500 BLUE
13000 GREEN
17000 YELLOW
25000 PURPLE
31001 WHITE
45897 BLACK

I want Sheet3 like below.(only those rows which were comman)

11000 RED
17000 YELLOW

Please suggest how to do this because I have more than 90000 records and I want to keep only Duplicate

[elayaz]

Hello.

While I hope that someone from forum will help you with an UDF since the records numbers is high,

Meanwhile see if the attached file kind of approach works.

[Sam9]

Thanks elayaz, for your help but not only numbers are high but there are more column up to G . So it is out of my brain. But thanks for help.

[GeneralDisarray]

In sheet 2, column C:

Just look for a match using this kind of formula:

=MATCH(A1,Sheet1!A:A,0)

IF it is a duplicate, then you the Match() function will return a number. IF the match function errors out (meaning there was no match) you will see #N/A (just sort or filter to get the ones you want).

[Sam9]

GeneralDisarray, Sorry First I said this is not what I needed because it was pasting some random numbers in Sheet2 Column which I could not understand the but actually it did what I needed after filter. My sheet has 65000 rows and Column from A to M and I put your formula in Column N. But now the problem is when I want to copy the Rows and columns of Sheet 2, I am getting error that Microsoft can not create or use the data range reference because it is too complex.

[GeneralDisarray]

What do you need to do with the rows (other than copy the matches to another sheet)? Maybe you can just act on sheet 2? If not, I would suggest just doing a copy of the sheet.

STEPS:
1) Right click on the tab-name, select "Move or Copy...", check the box for "Create a copy", then press OK.

2) Just sort the table ON COLUMN N. That will put all the non-duplicate values in a block (all the #N/A will be separated from the duplicates you want to isolate).

3) Highlight those rows, and delete them.

[elayaz]

Hello sam9,

See if something similar to attached file works for you.