I have created a formula in column Q which, after filled to the last row of
data identifies all of the duplicates by putting the SSN found in column K,
into the corresponding cell in column Q.
The formula is
=IF(AND(COUNTIF($P$2:$P$1740,P2)1,COUNTIF($G$2:$G $1740,G2)1),K2,"")

I need to write some code to delete all duplicates, except one.
Any suggestions are eagerly appreciated.
Paul

Do you want to delete all duplicates except the original and would you
want the entire row deleting?


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24057

Thanks for coming back to me,
If I have 2500 records, and 1000 of them should be unique, there are 1500
duplicates in addition to the 1000 that should be unique. I need to find all
identical records, some will have 1 duplicate, some 2 or more, and some may
have none.
When the process is completed, only the 1000 unique records will remain with
no duplicates.
The data is coming from a Mainframe download.
Hopefully, I have explained better
Thank you

"The Code Cage Team" wrote:


Do you want to delete all duplicates except the original and would you
want the entire row deleting?


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24057

you did explain well only you didnt say if the entire row with the
duplicates should be deleted?


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24057

You can use advanced filter with copy to another location option to filter
out unique records. Read more about it on this URL
http://www.contextures.com/xladvfilter01.html#FilterUR. Contextures has very
good information on other excel topics also.
--
Regards,



"PA" wrote:

Thanks for coming back to me,
If I have 2500 records, and 1000 of them should be unique, there are 1500
duplicates in addition to the 1000 that should be unique. I need to find all
identical records, some will have 1 duplicate, some 2 or more, and some may
have none.
When the process is completed, only the 1000 unique records will remain with
no duplicates.
The data is coming from a Mainframe download.
Hopefully, I have explained better
Thank you

"The Code Cage Team" wrote:


Do you want to delete all duplicates except the original and would you
want the entire row deleting?


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24057

See...
http://support.microsoft.com/KB/555375
--
Jim Cone
Portland, Oregon USA

Yes, the entire row should be deleted, leaving behind only unique rows.
Being used to doing things with formulas, and manually, I guess I make a lot
of assumptions that I do intuitively when working with the file.
If originally there are 3000 rows, and 2000 rows are unique and there are
also 500 rows each with a duplicate, when done there will be 2500 rows. This
is an example and please keep in mind that there may be some records with
more than one duplicate.
We get a new text file from the Data Center each week and never know how
many records are contained.

I hope this clarifies the situation.



"The Code Cage Team" wrote:


you did explain well only you didnt say if the entire row with the
duplicates should be deleted?


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24057

Thank you all for the feedback.
I fiddled around with Access over the weekend and came up with an excellent
method for identifying and deleting duplicates.
And I am particularly grateful to Jim Cone for teaching me how to ask a
question.

"PA" wrote:

I have created a formula in column Q which, after filled to the last row of
data identifies all of the duplicates by putting the SSN found in column K,
into the corresponding cell in column Q.
The formula is
=IF(AND(COUNTIF($P$2:$P$1740,P2)1,COUNTIF($G$2:$G $1740,G2)1),K2,"")

I need to write some code to delete all duplicates, except one.
Any suggestions are eagerly appreciated.
Paul