remove rows with the same reference
 

I have a workbook with 10 sheets. Each sheet will have
50,000 or more rows. Col A is a reference number for each
row of data. Reference numbers are repeated thoughout the
each sheet.

What I need to do is reduce each sheet to only 1 instance
of each unique reference number. I was able to compare
each row to the one above and delete it if the reference
is the same. (for example, each sheet will only contain
7,000 - 9,000 records after it is reviewed and takes 20
minutes or more.) While this works it is time-consuming
with over 500,000 rows to review.

Is there a better / quicker way to reduce each sheet to
display 1 instance of each unique value in col A?

Thanks for the help.

remove rows with the same reference
 

Yes, assuming the reference number is A1.

In B1, add. =COUNTIF($A$1:A1,A1), and copy down over all rows.

Then do a DataFilterAutoFilter on column B, and select a custom value <
1.

The duplicates will then show, so delete all visible rows, and remove the
filter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JT" wrote in message
...
I have a workbook with 10 sheets. Each sheet will have
50,000 or more rows. Col A is a reference number for each
row of data. Reference numbers are repeated thoughout the
each sheet.

What I need to do is reduce each sheet to only 1 instance
of each unique reference number. I was able to compare
each row to the one above and delete it if the reference
is the same. (for example, each sheet will only contain
7,000 - 9,000 records after it is reviewed and takes 20
minutes or more.) While this works it is time-consuming
with over 500,000 rows to review.

Is there a better / quicker way to reduce each sheet to
display 1 instance of each unique value in col A?

Thanks for the help.