Pivot Table

T

tzec76

have 600,000 rows of data. I would like to find how many duplicates are
there in this 3 coulmn of 600,000 rows of data. I would like to use Pivot
Table to do that. However, each column can contain only about 60,000 rows.

Can anyone help to find how to find how many duplicate data?
 
F

Fred

You are limited to just over 65000 rows per worksheet so...
Split your 600000 lines of data into several worksheets then to find the
duplicates in any single column of any worksheet:
Sort on that column (lets say colB)
In a spare column headed up 'Duplicate?' enter 'N' in the first row and the
formula
=if(B3=B2,"Y","N")
in the next row and copy down all rows.
Because the list is sorted, any duplicate entries will be on consecutive
rows and will be identified by the formula.

If you want to extract only the unique rows:
Copy the whole of the column containing the formula and Paste Special|Values
over the top (to remove the formula). You can now sort by this column to get
all unique ('N') rows together.

HTH
 
Top