(Remove) Duplicates in Excel -Help! 5

[RITec]

I have been given an Excel spreadsheet that has a list of addresses listed in column “ C”.
I need to find all duplicates just in column “C” and delete the row that contains the duplicate. There is about 1500 rows to search.

Any help is much appreciated.

Thanks in Advance

RITec

 

[SkipVought]

Hi,
1. Sort by col c
2. in a blank adjacent column enter the formula all the way down starting in row 2...

=if(c1=c2,0,1)

3. copy the entire column containing that formula and with the entire column selected do right-click and select Paste Special and select Values and OK -- this replaces the formulas with values of 0 and 1.
4. sort on the zeros and ones column
5. delete all the rows where the value is zero.

VOLA Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RITec]

Thank you Again Skip

Just what I needed

RITec

 

[amandarose80]

This is a GREAT time saver!!!
I export addresses and client names from our database all the time. There are a lot of children listed in it that do not need to get our mailings. I can now easily pick these out!
Glad I found this.

 

[Dsuperc]

If you got more than dups.... you may wish to try this.
I can't remember where I got it from so no praise for me.


Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through.
iListCount = Sheets("Sheet1&quot.Range("A1:A100&quot.Rows.Count
Sheets("Sheet1&quot.Range("A1&quot.Select
' Loop until end of records.
Do Until ActiveCell = ""
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the column number.
If ActiveCell.Row <> Sheets(&quot;Sheet1&quot.Cells(iCtr, 1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets(&quot;Sheet1&quot.Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets(&quot;Sheet1&quot.Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox &quot;Done!&quot;
End Sub

 

[MarkBeck]

I encounter this problem regulary with thousands of records. My preferred formula is as followes;

I will assume that the list is C1:C5000. The formula is for D1;

=if(countif(C1:C$5000,C1)>1,&quot;Not last&quot;,&quot;Last record&quot

Copy this formula to all cells in the D column untill the end of the list. Add a Filter (Select all desired columns, then from the menu go to Data>Filter>Auto Filter), set filter to &quot;Last Record&quot;. Now select the filtered list (click on the bottom right cell, hold down shift and click the uppermost left cell), right click and copy, go to another sheet and paste.
This is particalarly usefull where sorting proves challenging.



Additional tips.

I found the following functions usefull in managing lists.

Concatenate;
strings together several pieces of info. If you want to define records (Rows) according to several criteria, you can string together several cells in one using this function. you can use results in Filters. This is also particularly usefull in simplifying conditianal calculation where there are several conditions! Think of the numbers of &quot;IF's&quot; you could save!

Mid, Left, Right;
These select parts of a cell in a veriety of manners, i often have to tackle lists where the whole address is in a single cell and i have to seperate them componentially. The &quot;MID&quot; function allows you to define a starting point and one can add the use of &quot;Find&quot; (a particular word, sign, space, comma, etc.) or &quot;count&quot; (letters, etc.) in nitpicking the precise values from a long address string.