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
Etc.
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.

Thanks,
Achikam


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

Workbooks.Add
Set DataBook = ActiveSheet
ThisBook.Activate
R = 1

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


Hope this helps.
Mike