Finding repeated records???? 1

[MrProgrammer]

I have an Excel file which contains about 15000 records. Each record (row) has some fields(columns) such as Name, Lastname, Date of birth, Father's name, Mother's name and bla bla bla... Number of records are continuously increasing. At any instance of time, I want to find all the repeated records in the file with a macro written in Visual Basic(To say that two records are identical, their first five fields that I mentioned just before, must be same). To do this, I'm using a primitive method : From the beginning, I am comparing each record with all the other records so if there are N records, to find all the repeated records N! comparisons have to be made. This process takes a long time. Is there any other method to implement this job.

 

[SkipVought]

Hi, MrProgrammer,

You can improve you method by SORTING your table on the five columns you are using for comparison. Since Excel's built-in SORT wizard only allows for 3, you accomplish a 5-column sort by...

1. Selecting the 2 LEAST significant columns for the first sort and

2 selecting the 3 MOST significant columns for the final sort.

Then all you have to do is compare the current row with the previous row.

hope this helps Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

If your objective is to eliminate DUPLICATE records, then there is an EASY method - using Excel's DATABASE FUNCTIONS and EXTRACTION capability.

It would involve a fairly SIMPLE process of using Excel's Data - Filter - Advanced Filter - in VBA code - to extract all the UNIQUE records to a separate sheet, and then have the macro replace your existing data with the UNIQUE set of data.

If this is indeed your objective, email me and I'll send an example file via return email.

If, on the other hand, you need to know WHICH records are DUPLICATES, then it is another challenge. It can be done - with VBA code, but is more involved.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[MrProgrammer]

My objective is not to eliminate DUPLICATE records but I'm also concerned with using Excel's DATABASE FUNCTIONS and EXTRACTION capability. If you would send an example file to alpertufek@ixir.com, I really would be glad.

On the other hand, my real objective is to know WHICH records are DUPLICATES with VBA code because I'm managing the file via a user form.

 

[SkipVought]

MrProgrammer,

It seem obvious to me that DaleWatson123321 provided you with just about exactly what you were looking for.

NOW, I'd call THAT Helpful!

Perhaps you overlooked a very important step in the life-cycle of a thread.

Here at Tek-Tips, everyone benefits from being able to review contributions that have been identified as helpful or expert.

AND...
it is the ONLY recognition that such contributors recieve.

So, it would both show your appreciation in a tangable way and identify this posting as helpful or expert for others to benefit.

The way that you can accomplish this is to...

click the hyperlink in the lower left-hand corner of a contributor's posting; the hyperlink that states...

Click here to mark this
post as a helpful or expert
post!


Skip,
metzgsk@voughtaircraft.com

 

[AlaskanDad]

Here's an alternate (not real pretty) solution:

Insert two columns to the left of your data.

In cell B1: =C1&D1&E1&F1&G1 (drag down for all records)
In cell A1: =COUNTIF($B$1:$B$4,B1) (drag down for all records)

Now you can do a sort in column A for anything greater than 1.

Works for me.