deleting duplicate records macro 1

[ssphoenix]

Hi fellows,

I need to find or create a macro that will go and remove duplicate records based on certain criteria such as "if this cell is = to the previous" remove it"

I have tons of records pulled from access that I need to manipulate a bit.

Thanks for your help

 

[Dreamboat]

http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

That site has a formula. If you need to do this often, just record yourself inserting the formula and copying. I found other stuff recently for someone else by searching yahoo and typing the following (with quotes) into the search:

"delete duplicates"

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[marchristensen]

Great tip for searching on deleting duplicates. I was interested in doing this in Excel. Here are two good sites:

http://www.cpearson.com/excel/deleting.htm

http://www.pcmag.com/article/0,2997,s%3D1479%26a%3D5632,00.asp

 

[Dreamboat]

I knew Chip (cpearson site) had something, but couldn't find it... thanks march

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[ssphoenix]

Hi fellows again,

Thanks for the many replay you've posted. I did find this web site with several macros. However, it only shows how to deleted/compare two columns and in many cases within the column.

Any macros or ways to compare several columns (perhaps I can have user interaction giving the column number), would be most appreciated.

I could also do in Access if you could point me to similar queries.

 

[Dreamboat]

Sure, create a new Find Duplicates query. After it's created and you know it's working right, open the query in design view and hit (from menu) Query-Delete query. Then run it.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[JVFriederick]

I'll throw in my two cents worth.

Assume you have headings in row 1.
1) Say you have 3 columns that together comprise the "record" you wish to determine is duplicated. Pick an empty column #4 and concatenate the 3 columns : A2&B2&C2. This is to create a record key.
2) Now sort the table based on the key in field 4.
3) Now write an equation in a new column #5. Start in row 3 : =IF(D2=D3,1,0). This will generate a 1 whenever the record has been duplicated. Copy down for all records, then copy and paste as values (we need to freeze the relationship).
4) Now sort all 5 columns based on column #5, leave off row 2, in ascending order.

The records at the bottom, which are duplicates, can be easily deleted. If you may have leading or trailing spaces in your data, these can be caught by using =Trim(A2)&Trim(B2)&Trim(C2) in the concatenate equation. If you need the remaining records back in original order for some reason, use a counter column before step 1 and sort this column along with the other steps.

This technique works for any number of columns.