making a list

D

Dewi...

Hello

How can I get excel to look up a column, copy the data over to another
worksheet, deleting all the duplicate names. So I have a nice list of
names.

Or some pointers would do.

Thanks
 
B

Billy Liddel

Dewi

Biff (T.Valco) wrote a formula for this under "unique values" I think.
However, a macro will do the same. The following code will ceate a list on
the same page two column to the right of your current data, so make sure that
you do not have any data in this column.

Select your column and run the code after you have pasted it into a VB
Module. (ALT + F11, Insert, Module)

Sub listUnique()

Set rng = Selection
l = Selection.Rows.count
r = ActiveCell.Row: startR = ActiveCell.Row
col = ActiveCell.CurrentRegion.Columns.count + 2

Set lst = Range(Cells(startR, col), Cells(r, col))
For Each d In rng
Set lst = Range(Cells(startR, col), Cells(r, col))
x = Application.Match(d, lst, 0)
If IsError(x) Then
Cells(r, col) = d
r = r + 1
End If
Next

End Sub

Regards
Peter
 
D

Dewi...

Hello

How can I get excel to look up a column, copy the data over to another
worksheet, deleting all the duplicate names. So I have a nice list of
names.

Or some pointers would do.

Thanks
Click to expand...

Thanks to all, i'm nearly there :)
 
Top