Variance in an array

[DejaVu]

In one column, there are values like:
Andy
Bob
Cindy
Dan
Ed
etc...

I'm wanting some sort of procedure to go through this column, find how
many variations there are and store that number as a variable. Also,
I'd like to be able to store all of the string values into an array to
be used later. For example; if the above was my data, there would be
five variations (Andy, Bob, Cindy, Dan, Ed).
Then use that array to take all the rows that have "Andy" in them and
move them to a sheet that will be created named "Andy". And loop
through each name.

I've worked on a somewhat similar project before, but I'm having
trouble figuring this one out. Here is the code I used before that
would look for one word and move all those rows to another column.
This is code that was previously suggested to me on this message board
(Thanks Jim Thomlinson!). 'Link'
(http://www.excelforum.com/showthread.php?t=385313)

Sub CopyCells()
Dim rngFirst As Range, rngCurrent As Range, rngFoundCells As Range
Dim rngToSearch As Range, rngToPaste As Range
Dim wksToSearch As Worksheet, wksToPaste As Worksheet

Set wksToSearch = Sheets("Rough")
Set wksToPaste = Worksheets.Add(, Sheets.Count)
Set rngToSearch = wksToSearch.Cells
Set rngToPaste = wksToPaste.Range("A65536").End(xlUp).Offset(1, 0)
Set rngCurrent = rngToSearch.Find(strWordToFind, , , xlWhole)

If rngCurrent Is Nothing Then
MsgBox strWordToFind & " was not found"
Else
Set rngFirst = rngCurrent
Set rngFoundCells = rngCurrent.EntireRow
Do
Set rngFoundCells = Union(rngCurrent.EntireRow,
rngFoundCells)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngFirst.Address = rngCurrent.Address
rngFoundCells.Copy rngToPaste
rngFoundCells.Delete
End If

End Sub

TIA for all help!

DejaVu

 

[STEVE BELL]

You can do this with Advance filters.
To get the code just record macro(s) while you perform it,
than edit the code to simplify it.

Do an advanced filter on the name column and put the results in another
place using Unique
Records. Than just count the number (you can erase this at anytime through
code.)

But you can use that list to perform Advanced Filter on the database and
hide all but one of the names. You can either have the filter put the data
elsewhere or filter it in place. Than use Edit > Goto > Special > visible
cells only, copy and paste.

And you should be able to do all of this in code without any selecting...

 

[Tom Ogilvy]

Ron de Bruin has already written sample code for this:

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