making a list

  • Thread starter Thread starter Dewi...
  • Start date Start date
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
 
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
 
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 :-)
 
Back
Top