I am using Excel 2003. I am working with a list of names of various people,
in one column. I need to have this list reproduced on a blank worksheet with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

....and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?

On Mar 25, 1:34*pm, sycsummit
wrote:
I am using Excel 2003. *I am working with a list of names of various people,
in one column. *I need to have this list reproduced on a blank worksheet with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

...and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?

Yes, there is a way!

Paste this into a new module and hit F5:


'Code
Option Explicit

Public Sub CountDuplicates()

'Declarations
Dim strCellText() As String
Dim strCellUnique() As String

Dim Cell As Range
Dim iCounter As Integer
Dim jCounter As Integer
Dim iNumCells As Integer
Dim iNumDups As Integer
Dim MSG As String
Dim bnDup As Boolean
Dim strSheetName As String
Dim strNewName As String


'Get array of all unique values
iCounter = 1
For Each Cell In Selection

bnDup = False
ReDim Preserve strCellText(iCounter)
strCellText(iCounter) = Cell

For jCounter = 1 To iNumCells
If strCellText(iCounter) = strCellText(jCounter) Then
bnDup = True
End If
Next jCounter

If bnDup = False Then
iNumCells = iNumCells + 1
ReDim Preserve strCellUnique(iNumCells)
strCellUnique(iNumCells) = Cell
End If

iCounter = iCounter + 1
Next Cell


'Get sheet names
strSheetName = ActiveWorkbook.ActiveSheet.Name
strNewName = "NewSheet" & CStr(ActiveWorkbook.Worksheets.Count)

'See if sheet exists, create if it doesn't
If WorksheetExists(strNewName, ActiveWorkbook) Then
Call MsgBox("Rename sheet " & strNewName & ".", vbOKOnly,
"Error")
Exit Sub
Else

ActiveWorkbook.Worksheets.Add.Name = strNewName
Sheets(strNewName).Move
After:=Sheets(ActiveWorkbook.Worksheets.Count)
End If

'Copy and paste
Sheets(strNewName).Activate

For iCounter = 1 To iNumCells
Cells(iCounter, 1) = strCellUnique(iCounter)
Next iCounter



End Sub
Function WorksheetExists(SheetName As String, Optional WhichBook As
Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

'End of code


HTH

Chris

Here is some code that I use. It requires a reference to the Microsoft
Scripting Runtime library. In the VBE Tools - References - check Microsoft
Scripting Runtime.

Private Sub GetUniqueItems()
Dim cell As Range 'Current cell in range to check
Dim rngToSearch As Range 'Cells to be searched
Dim dic As Scripting.Dictionary 'Dictionary Object
Dim dicItem As Variant 'Items within dictionary object
Dim wks As Worksheet 'Worksheet to populate with
unique items
Dim rngPaste As Range 'Cells where unique items are
placed

Application.ScreenUpdating = False
'Create range to be searched
Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell

'Confirm there is a relevant range selected
If Not rngToSearch Is Nothing Then
'Create dictionay object
Set dic = New Scripting.Dictionary

'Populate dictionary object with unique items (use key to define
unique)
For Each cell In rngToSearch 'Traverse selected range
If Not dic.Exists(cell.Value) And cell.Value < Empty Then
'Check the key
dic.Add cell.Value, cell.Value 'Add the item if unique
End If
Next

If Not dic Is Nothing Then 'Check for dictionary
Set wks = Worksheets.Add 'Create worksheet to populate
Set rngPaste = wks.Range("A1") 'Create range to populate
For Each dicItem In dic.Items 'Loop through dictionary
rngPaste.NumberFormat = "@" 'Format cell as text
rngPaste.Value = dicItem 'Add items to new sheet
Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range
Next dicItem
'Clean up objects
Set wks = Nothing
Set rngPaste = Nothing
Set dic = Nothing
End If
End If
Application.ScreenUpdating = True
End Sub
--
HTH...

Jim Thomlinson


"sycsummit" wrote:

I am using Excel 2003. I am working with a list of names of various people,
in one column. I need to have this list reproduced on a blank worksheet with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

...and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?

Sorry... to use the code I posted just select the column that the names are
in and run the code.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Here is some code that I use. It requires a reference to the Microsoft
Scripting Runtime library. In the VBE Tools - References - check Microsoft
Scripting Runtime.

Private Sub GetUniqueItems()
Dim cell As Range 'Current cell in range to check
Dim rngToSearch As Range 'Cells to be searched
Dim dic As Scripting.Dictionary 'Dictionary Object
Dim dicItem As Variant 'Items within dictionary object
Dim wks As Worksheet 'Worksheet to populate with
unique items
Dim rngPaste As Range 'Cells where unique items are
placed

Application.ScreenUpdating = False
'Create range to be searched
Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell

'Confirm there is a relevant range selected
If Not rngToSearch Is Nothing Then
'Create dictionay object
Set dic = New Scripting.Dictionary

'Populate dictionary object with unique items (use key to define
unique)
For Each cell In rngToSearch 'Traverse selected range
If Not dic.Exists(cell.Value) And cell.Value < Empty Then
'Check the key
dic.Add cell.Value, cell.Value 'Add the item if unique
End If
Next

If Not dic Is Nothing Then 'Check for dictionary
Set wks = Worksheets.Add 'Create worksheet to populate
Set rngPaste = wks.Range("A1") 'Create range to populate
For Each dicItem In dic.Items 'Loop through dictionary
rngPaste.NumberFormat = "@" 'Format cell as text
rngPaste.Value = dicItem 'Add items to new sheet
Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range
Next dicItem
'Clean up objects
Set wks = Nothing
Set rngPaste = Nothing
Set dic = Nothing
End If
End If
Application.ScreenUpdating = True
End Sub
--
HTH...

Jim Thomlinson


"sycsummit" wrote:

I am using Excel 2003. I am working with a list of names of various people,
in one column. I need to have this list reproduced on a blank worksheet with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

...and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?

I believe this macro will do what you want...

Sub MoveUniqueNames()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet2").Cells(Z, "A").Value = .Cells(X, "A").Value
Z = Z + 1
End If
Next
End With
End Sub

It assumes the worksheet with your original (repeated) name list is Sheet1
(in Column A starting at Row 1) and the worksheet you want to put the unique
name list on is Sheet2 (into Column A starting at Row 1).

Rick


"sycsummit" wrote in message
...
I am using Excel 2003. I am working with a list of names of various
people,
in one column. I need to have this list reproduced on a blank worksheet
with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

...and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?

All of these responses look impressive, but I was hoping there would be a
more simple solution, such as a function I may have overlooked... anything
that would let me type "=(formula)" in the cell and be done with it.

I can work with this though... but where do you put this? How do I input
this stuff into my spreadsheet?


"Rick Rothstein (MVP - VB)" wrote:

I believe this macro will do what you want...

Sub MoveUniqueNames()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet2").Cells(Z, "A").Value = .Cells(X, "A").Value
Z = Z + 1
End If
Next
End With
End Sub

It assumes the worksheet with your original (repeated) name list is Sheet1
(in Column A starting at Row 1) and the worksheet you want to put the unique
name list on is Sheet2 (into Column A starting at Row 1).

Rick


"sycsummit" wrote in message
...
I am using Excel 2003. I am working with a list of names of various
people,
in one column. I need to have this list reproduced on a blank worksheet
with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

...and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?

This response works with my code... I did not check it against the other
postings. If you are not already in the VB editor, press Alt+F11 from any
worksheet to go there. Once there, click on Insert/Module from the VB editor
menu bar and then Copy/Paste my code (repeated here for your convenience)

Sub MoveUniqueNames()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet4").Cells(Z, "A").Value = .Cells(X, "A").Value
Z = Z + 1
End If
Next
End With
End Sub

into the code window that opened there. You can execute the code from any
worksheet, but my guess is you will want to be in Sheet2 (where my code
places the unique names that are listed in Sheet1 starting at Column A, Row
1) in order to see the list being produced; so, go to Sheet2 and then press
Alt+F8 and select MoveUniqueNames from the list, then click on Run. You
should see the unique names listed on Sheet2 starting at Column A, Row 1.

Rick


"sycsummit" wrote in message
...
All of these responses look impressive, but I was hoping there would be a
more simple solution, such as a function I may have overlooked... anything
that would let me type "=(formula)" in the cell and be done with it.

I can work with this though... but where do you put this? How do I input
this stuff into my spreadsheet?


"Rick Rothstein (MVP - VB)" wrote:

I believe this macro will do what you want...

Sub MoveUniqueNames()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet2").Cells(Z, "A").Value = .Cells(X, "A").Value
Z = Z + 1
End If
Next
End With
End Sub

It assumes the worksheet with your original (repeated) name list is
Sheet1
(in Column A starting at Row 1) and the worksheet you want to put the
unique
name list on is Sheet2 (into Column A starting at Row 1).

Rick


"sycsummit" wrote in message
...
I am using Excel 2003. I am working with a list of names of various
people,
in one column. I need to have this list reproduced on a blank
worksheet
with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

...and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?

Thanks for the direction... I get the concept. But, I am unfamiliar with
this language and syntax!

How would I have to change your code if I wanted to read the whole list of
names from a worksheet titled "NEW", from cells J1 through J25 -- and paste
them in a worksheet called "Billing", as my list of one of each name,
starting with cell A5?

"Rick Rothstein (MVP - VB)" wrote:

This response works with my code... I did not check it against the other
postings. If you are not already in the VB editor, press Alt+F11 from any
worksheet to go there. Once there, click on Insert/Module from the VB editor
menu bar and then Copy/Paste my code (repeated here for your convenience)

Sub MoveUniqueNames()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet4").Cells(Z, "A").Value = .Cells(X, "A").Value
Z = Z + 1
End If
Next
End With
End Sub

into the code window that opened there. You can execute the code from any
worksheet, but my guess is you will want to be in Sheet2 (where my code
places the unique names that are listed in Sheet1 starting at Column A, Row
1) in order to see the list being produced; so, go to Sheet2 and then press
Alt+F8 and select MoveUniqueNames from the list, then click on Run. You
should see the unique names listed on Sheet2 starting at Column A, Row 1.

Rick


"sycsummit" wrote in message
...
All of these responses look impressive, but I was hoping there would be a
more simple solution, such as a function I may have overlooked... anything
that would let me type "=(formula)" in the cell and be done with it.

I can work with this though... but where do you put this? How do I input
this stuff into my spreadsheet?


"Rick Rothstein (MVP - VB)" wrote:

I believe this macro will do what you want...

Sub MoveUniqueNames()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet2").Cells(Z, "A").Value = .Cells(X, "A").Value
Z = Z + 1
End If
Next
End With
End Sub

It assumes the worksheet with your original (repeated) name list is
Sheet1
(in Column A starting at Row 1) and the worksheet you want to put the
unique
name list on is Sheet2 (into Column A starting at Row 1).

Rick


"sycsummit" wrote in message
...
I am using Excel 2003. I am working with a list of names of various
people,
in one column. I need to have this list reproduced on a blank
worksheet
with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

...and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?

I generalized the code so you can modify it easily in the future in that
need should ever arise. There are 6 constant (Const) statements toward the
top of the code that controls where the names will be read from and where
they will be written to. The Const names should be fairly self-explanatory,
so you should be able to change the setup at will. One comment on your "J1
through J25" statement. The code, as written, does not need to know how many
names there are in the list... it will read down to the last filled-in cell
in the SourceColumn.

Rick

Sub MoveUniqueNames()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
Const SourceColumn As String = "J"
Const SourceStartRow As Long = 1
Const DestinationColumn As String = "A"
Const DestinationStartRow As Long = 5
Const SourceSheet As String = "NEW"
Const UniqueSheet As String = "Billing"
UniqueNames = "*"
Z = DestinationStartRow
With Worksheets(SourceSheet)
For X = SourceStartRow To .Cells(.Rows.Count, _
SourceColumn).End(xlUp).Row
If .Cells(X, SourceColumn) < "" Then
If InStr(UniqueNames, "*" & _
.Cells(X, SourceColumn).Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, SourceColumn).Value & "*"
Worksheets(UniqueSheet).Cells(Z, DestinationColumn).Value = _
.Cells(X, SourceColumn).Value
Z = Z + 1
End If
End If
Next
End With
End Sub



"sycsummit" wrote in message
...
Thanks for the direction... I get the concept. But, I am unfamiliar with
this language and syntax!

How would I have to change your code if I wanted to read the whole list of
names from a worksheet titled "NEW", from cells J1 through J25 -- and
paste
them in a worksheet called "Billing", as my list of one of each name,
starting with cell A5?

"Rick Rothstein (MVP - VB)" wrote:

This response works with my code... I did not check it against the other
postings. If you are not already in the VB editor, press Alt+F11 from any
worksheet to go there. Once there, click on Insert/Module from the VB
editor
menu bar and then Copy/Paste my code (repeated here for your convenience)

Sub MoveUniqueNames()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet4").Cells(Z, "A").Value = .Cells(X, "A").Value
Z = Z + 1
End If
Next
End With
End Sub

into the code window that opened there. You can execute the code from any
worksheet, but my guess is you will want to be in Sheet2 (where my code
places the unique names that are listed in Sheet1 starting at Column A,
Row
1) in order to see the list being produced; so, go to Sheet2 and then
press
Alt+F8 and select MoveUniqueNames from the list, then click on Run. You
should see the unique names listed on Sheet2 starting at Column A, Row 1.

Rick


"sycsummit" wrote in message
...
All of these responses look impressive, but I was hoping there would be
a
more simple solution, such as a function I may have overlooked...
anything
that would let me type "=(formula)" in the cell and be done with it.

I can work with this though... but where do you put this? How do I
input
this stuff into my spreadsheet?


"Rick Rothstein (MVP - VB)" wrote:

I believe this macro will do what you want...

Sub MoveUniqueNames()
Dim X As Long
Dim Z As Long
Dim UniqueNames As String
UniqueNames = "*"
Z = 1
With Worksheets("Sheet1")
For X = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If InStr(UniqueNames, "*" & .Cells(X, "A").Value & "*") = 0 Then
UniqueNames = UniqueNames & .Cells(X, "A").Value & "*"
Worksheets("Sheet2").Cells(Z, "A").Value = .Cells(X,
"A").Value
Z = Z + 1
End If
Next
End With
End Sub

It assumes the worksheet with your original (repeated) name list is
Sheet1
(in Column A starting at Row 1) and the worksheet you want to put the
unique
name list on is Sheet2 (into Column A starting at Row 1).

Rick


"sycsummit" wrote in message
...
I am using Excel 2003. I am working with a list of names of various
people,
in one column. I need to have this list reproduced on a blank
worksheet
with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

...and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?

All of these responses look impressive, but I was hoping there would be a
more simple solution, such as a function I may have overlooked... anything
that would let me type "=(formula)" in the cell and be done with it.

I can work with this though... but where do you put this? How do I input
this stuff into my spreadsheet?

"sycsummit" wrote:

I am using Excel 2003. I am working with a list of names of various people,
in one column. I need to have this list reproduced on a blank worksheet with
the repeated names removed.

for instance, the list I'm working with would be something like:
Pat
Pat
Dan
Marie
Marie
Sharron
Sharron
Sharron
Sharron
Daniel
Mark
Mark
Mark
Mark

...and I would need this list to be reproduced on another tab as:
Pat
Dan
Marie
Sharron
Daniel
Mark

Is there a way I can do this?