MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting Double Info

Posted by Achi on August 11, 2000 8:26 AM

I have a list in one column of British Zip Codes many of which are repetitive e.g.
E1 1LU
E1 1LU
E1 4PN
E1 4PN
E1 6AA
E1 6AA
E1 6AN
E1 6AN
I want a list with one zip code form each. How do I delete the redundant zip codes in a column of over 4000. Some of the Zip Codes appear more than twice.


Posted by Michael Liu on August 11, 0100 3:49 PM

There's actually some way of doing this in Excel, but
before I found that (and I don't recall what it is
anymore), I wrote a little program to do what you are
asking. It's not the most elegant piece of code, but
it works.

I actually have a whole series of these that I can
call upon through a form that will do any of the
following operations:

Add/subtract/multiply/divide all number cells in
selected range by X.
Trim leading/trailing spaces in text cells in selected range.
Proper case all text cells in selected range.
Generate a list of all unique entries in selected range.

Here's the code bit that does the unique list:

Usage: select the cells in one column that have the
data you wish to draw a unique list from. Then run
the UniqueList macro, which creates an alphabetized
list of all the unique cells in a new workbook.

Sub UniqueList()
Application.ScreenUpdating = False
Dim ThisBook, DataBook, sR As Integer, sC As Integer, R As Integer

Set ThisBook = ActiveWorkbook
sR = Selection.Row
sC = Selection.Column

Set DataBook = ActiveSheet
R = 1

On Error GoTo Finish
DataBook.Cells(R, 1).Value = ActiveCell.Value
R = R + 1
Cells(sR, sC).Select
Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True
End Sub

Hope this helps.