Copy/Paste duplicates from multiple columns

Hi guys,

So I have 2 massive excel files (around 40,000 rows each). I have column A-O in both files that need to be compared for duplicates, and then paste these duplicates somewhere else so I can double check. So, is there a macro that compares these columns and pastes the duplicates that it finds?
I'd appreciate anyone's help an input. I work on a mac and have excel 2011.

Thanks guys!

Could you explain the formula? Thanks!

I've been trying to work on it and I think this is a better way of tackling it. I would want to copy and paste the duplicates in a sheet called "Duplicates' (workbook 1) if all the data in column C, D, E, G, H and I in a sheet called "A-L" (workbook 1) is exactly the same as the data in columns C,D,E,G,H,I in a sheet called "A-L" (seperate, workbook 2).

Any suggestions greatly appreciated.

Cheers,
R

Hi xladept,

Firstly THANKS!
Here is a bit more information because Im not sure what to put where when trying to replicate this.

So, I have workbook 1: 2 Sheets: "A-L" and "M-Z"
workbook 2: 2 Sheets: "A-L" and "M-Z"

An entry is considered a duplicate if the data in columns C - E and G-O are exactly the same

I need to check for duplicates within each sheet AND check if there are duplicates in "A-L" from sheet 1 when compared to "A-L" from sheet 2, and same thing for "M-Z"

"A-L" on workbook 1 has 27,949 rows
"A-L" on workbook 2 has 55,288 rows

"M-Z" on workbook 1 has 22,829
"M-Z" on workbook 2 has 46,859

I guess I can just eliminate the duplicate if the entry is exactly the same instead of copying and pasting the duplicates and checking them myself. I'm anticipating a substantial amount of duplicates so going though them myself anyway would defeat the purpose. That's why the data in all those columns needs to match up.

Let me know if you need more information and unfortunately I cannot upload the files they contain super sensitive information. Thanks guys I appreciate your help. As you can see these files are massive.

Cheers,
R

When I try to run the copy, it tells me its an invalid next control variable reference. Any ideas? It also highlights the the name subAOCullcopy() in yellow.

Thanks so much for your help again

they are sorted by Last name. Also for this I should have a workbook named "duplicates" right?

It still gives me the same error message as before. And yes sorted by last names in Column C. I am also writing this code in the Duplicates workbook. I dont know if that makes a difference

---------- Post added at 06:06 PM ---------- Previous post was at 06:00 PM ----------

Also it now says "sub or function not defined" and highlights Dupes in Dumpduped1. I'm thinking it should be Dupes1 or dupes2

Thanks again so much

For both now it says "subscript out of range" Im so sorry for asking so many questions!

Cheers

Yeah they are names Workbook 1 and Workbook 2. and all the sheets are A-L and M-Z.

Everything seems to be exactly the same. "Duplicates" "Workbook 1" and "Workbook 2" I have another sheet on both workbooks, but that is not pertinent to the duplicate search. No no highlights everywhere. I will give it a look again, but both codes give me the same error.

Thanks again so much

Still no luck, gives me the same error. Where should I look for something that might cause this?

Thanks for taking the time.

Cheers,
R

Yeah that's strange. I triple checked the names and they match, could it be an array error? No Im just running the macro as is. Missed a couple of 15's? where exactly?

Cheers,
R

Changed all the 14's to 15's, for some reason it not even letting me single step with with F8. I don't know if the fact that I have a mac makes a difference.

Again, many thanks