need list of unique values from array in memory
 

I'm working on a project, where I have several thousand records in an excel
sheet (each has 30+ columns of data). I'm doing various data manipulations,
and all is going well so far.

However, my co-worker/customer has asked for a new report, with the data
segmented by the person who is responsible for each record. So, I have maybe
less than 100 people that repeat throughout my thousands of records. To
dynamically keep my code working, I need to pull a list of all responsible
people from the data itself. So, I'm looking for a way to identify each
"new" name as I cycle through the list.

My approach is to go through each record and compare the name to a shorter
list of the names I've already found, e.g.

for i= 1 to 5000
tempName = MyArray(i,14) 'get the name for that record
for p= 1 to 100 'my list of unique names
if tempName = FoundNames(p) then
exit for 'if I find the name, just
else
FoundName = False
end if
next
If FoundName = False then
FoundNamesCount = FoundNamesCount + 1
FoundNames(FoundNamesCount) = tempName
End if
next

But I'm thinking there must be a better (and faster) way....
Suggestions?

Thanks,
Keith
--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

need list of unique values from array in memory
 

On a Backup Copy of your file try this:
Post the below code into a standard module

Prior to running this macro Select or Highlight your Column 14 (N) data from
Row 2down to the end
Copys your unique reps to Column AZ

Sub GetUnique()
Dim rng As Range
TempName = Selection.Offset(-1, 0).Resize(1, 1).Value
If Selection.Columns.Count 1 Then
MsgBox "Please select cells in one column only"
Exit Sub
End If
If Selection.Row = 1 Then
MsgBox "Selection cannot include row 1"
Exit Sub
End If
Set rng = Selection.Offset(-1, 0) _
.Resize(Selection.Rows.Count + 1, 1)
rng(1).Value = "TempHeader"
Columns("AZ;AZ").Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("AZ1"), Unique:=True
Range("AZ1").Delete shift:=xlUp
rng(1).Value = ""
Range("AZ1").CurrentRegion.Sort Key1:=Range("AZ1"), _
Order1:=xlAscending, Header:=xlNo
Range("N1").Value = TempName <<<<< Note VERIFY YOUR REP
HEADER IS CURRENTLY CELL N1
End Sub


"KR" wrote in message
...
I'm working on a project, where I have several thousand records in an
excel
sheet (each has 30+ columns of data). I'm doing various data
manipulations,
and all is going well so far.

However, my co-worker/customer has asked for a new report, with the data
segmented by the person who is responsible for each record. So, I have
maybe
less than 100 people that repeat throughout my thousands of records. To
dynamically keep my code working, I need to pull a list of all responsible
people from the data itself. So, I'm looking for a way to identify each
"new" name as I cycle through the list.

My approach is to go through each record and compare the name to a shorter
list of the names I've already found, e.g.

for i= 1 to 5000
tempName = MyArray(i,14) 'get the name for that record
for p= 1 to 100 'my list of unique names
if tempName = FoundNames(p) then
exit for 'if I find the name, just
else
FoundName = False
end if
next
If FoundName = False then
FoundNamesCount = FoundNamesCount + 1
FoundNames(FoundNamesCount) = tempName
End if
next

But I'm thinking there must be a better (and faster) way....
Suggestions?

Thanks,
Keith
--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

need list of unique values from array in memory
 

Hi,

Try the advanced filter command to generate the list of the unique values of
a field.

Range("F2:F10").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"H2"), Unique:=True

Mika Oukka

"KR" wrote in message
...
I'm working on a project, where I have several thousand records in an
excel
sheet (each has 30+ columns of data). I'm doing various data
manipulations,
and all is going well so far.

However, my co-worker/customer has asked for a new report, with the data
segmented by the person who is responsible for each record. So, I have
maybe
less than 100 people that repeat throughout my thousands of records. To
dynamically keep my code working, I need to pull a list of all responsible
people from the data itself. So, I'm looking for a way to identify each
"new" name as I cycle through the list.

My approach is to go through each record and compare the name to a shorter
list of the names I've already found, e.g.

for i= 1 to 5000
tempName = MyArray(i,14) 'get the name for that record
for p= 1 to 100 'my list of unique names
if tempName = FoundNames(p) then
exit for 'if I find the name, just
else
FoundName = False
end if
next
If FoundName = False then
FoundNamesCount = FoundNamesCount + 1
FoundNames(FoundNamesCount) = tempName
End if
next

But I'm thinking there must be a better (and faster) way....
Suggestions?

Thanks,
Keith
--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.