Countif across workbooks

Hi,

I have a project with 2 million rows of data (13 digit numbers, single column). I need a method to find duplicates among this population. But I'm not sure how to run a countif function because Excel 2013 limits rows to a little over 1 million and hence my data is in two workbooks to maintain some sanity. I did search the forum for a similar problem, but failed to find one. Please be kind.

OK, a lot of numbers. But why two workbooks? Why not two worksheets or even just two columns?

In the following example, I have a 5 x 5 matrix with some duplicates:

HTML Code: 

The formula in cell H1 is =COUNTIF($A$1:$E$5,A1)

So, assuming your data was in a two column matrix on a single sheet, you could use that in a secondary matrix or use: =COUNTIF($A$1:$E$5,A1)>1 in Conditional Formatting.

Or, if you used: =IF(COUNTIF($A$1:$E$5,A1)>1,"duplicate","") in a secondary matrix, you could filter on "duplicate" in either of the secondary matrix columns.


Regards, TMS

Maybe this is one way..But 1 million rows...

In sheet1 B2 and copy down this.

=(COUNTIF($A$1:$A$10000000,A1)+COUNTIF(Sheet2!$A$1:$A$10000000,A1))>1


In sheet2 B2 and copy down this.

=(COUNTIF($A$1:$A$10000000,A1)+COUNTIF(Sheet1!$A$1:$A$10000000,A1))>1

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

.......................

@vivekmartin: how went it?

Regards, TMS

Glad we could help.