Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique values in an array. | Excel Discussion (Misc queries) | |||
Returning an array of unique values? | Excel Worksheet Functions | |||
Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
create an array with unique items IN MEMORY | Excel Worksheet Functions | |||
Frequncy of unique values in array | Excel Programming |