Autofilter question-s there a way to reference Adam

If you run an autofilter in column A and the drop down shows:
Adam
Bob
Dave
George
Mark
Steven
etc

Is there a way to reference Adam as 1 or something? Bob would be 2 Dave
would be 3 etc.

What I'm thinking is something like autofilter A and then criteria
would be Adam so I can copy and paste to a new sheet. Right now I have
maybe 800 different names and I'm trying to figure out an easier way to
move each individual entry and the number will only go up.

The drop down list in auto-filter is an internal Excel function which you
cannot access other than through the user-interface. You could try using
the advanced filter and copy the result to another location removing
duplicates by selecting unique records only, then sorting the list ? Or in
VBA you create a new collection and sort that, collections do not allow
duplicate keys. Example follows...where a collection called 'NoDupes' is
created, and sorted

Sub RemoveDuplicates()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items list are in A2:A106
Set AllCells = Range("A2:A106")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!

On Error Resume Next
For Each Cell In AllCells
If Not Cell.EntireRow.Hidden Then
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
End If
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i
End Sub

--
Cheers
Nigel



"JeffMelton" <[email protected]> wrote in message
news:[email protected]...
> If you run an autofilter in column A and the drop down shows:
> Adam
> Bob
> Dave
> George
> Mark
> Steven
> etc
>
> Is there a way to reference Adam as 1 or something? Bob would be 2 Dave
> would be 3 etc.
>
> What I'm thinking is something like autofilter A and then criteria
> would be Adam so I can copy and paste to a new sheet. Right now I have
> maybe 800 different names and I'm trying to figure out an easier way to
> move each individual entry and the number will only go up.
>

Insert a <<new>> column to the left of the Name Column
and enter (same row as first record name which is in say d4) into c4
=subtotal(3,$D$4:D4) and Copy down to D804. This should furnish
sequential numbering whether your table is filtered or not, and whether
rows are added or deleted.
HTH
Jim May

"JeffMelton" <[email protected]> wrote in message
news:[email protected]...
> If you run an autofilter in column A and the drop down shows:
> Adam
> Bob
> Dave
> George
> Mark
> Steven
> etc
>
> Is there a way to reference Adam as 1 or something? Bob would be 2 Dave
> would be 3 etc.
>
> What I'm thinking is something like autofilter A and then criteria
> would be Adam so I can copy and paste to a new sheet. Right now I have
> maybe 800 different names and I'm trying to figure out an easier way to
> move each individual entry and the number will only go up.
>

You can use the code from John Walkenbach's site posted by Nigel to get a
unique list, or you can use code from Ron de Bruin's site which includes code
to do the copying as well.

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"Nigel" wrote:

> The drop down list in auto-filter is an internal Excel function which you
> cannot access other than through the user-interface. You could try using
> the advanced filter and copy the result to another location removing
> duplicates by selecting unique records only, then sorting the list ? Or in
> VBA you create a new collection and sort that, collections do not allow
> duplicate keys. Example follows...where a collection called 'NoDupes' is
> created, and sorted
>
> Sub RemoveDuplicates()
> Dim AllCells As Range, Cell As Range
> Dim NoDupes As New Collection
> Dim i As Integer, j As Integer
> Dim Swap1, Swap2, Item
>
> ' The items list are in A2:A106
> Set AllCells = Range("A2:A106")
>
> ' The next statement ignores the error caused
> ' by attempting to add a duplicate key to the collection.
> ' The duplicate is not added - which is just what we want!
>
> On Error Resume Next
> For Each Cell In AllCells
> If Not Cell.EntireRow.Hidden Then
> NoDupes.Add Cell.Value, CStr(Cell.Value)
> ' Note: the 2nd argument (key) for the Add method must be a string
> End If
> Next Cell
>
> ' Resume normal error handling
> On Error GoTo 0
>
> ' Sort the collection (optional)
> For i = 1 To NoDupes.Count - 1
> For j = i + 1 To NoDupes.Count
> If NoDupes(i) > NoDupes(j) Then
> Swap1 = NoDupes(i)
> Swap2 = NoDupes(j)
> NoDupes.Add Swap1, before:=j
> NoDupes.Add Swap2, before:=i
> NoDupes.Remove i + 1
> NoDupes.Remove j + 1
> End If
> Next j
> Next i
> End Sub
>
> --
> Cheers
> Nigel
>
>
>
> "JeffMelton" <[email protected]> wrote in message
> news:[email protected]...
> > If you run an autofilter in column A and the drop down shows:
> > Adam
> > Bob
> > Dave
> > George
> > Mark
> > Steven
> > etc
> >
> > Is there a way to reference Adam as 1 or something? Bob would be 2 Dave
> > would be 3 etc.
> >
> > What I'm thinking is something like autofilter A and then criteria
> > would be Adam so I can copy and paste to a new sheet. Right now I have
> > maybe 800 different names and I'm trying to figure out an easier way to
> > move each individual entry and the number will only go up.
> >
>
>
>