Stuck on this: building array of only unique column values

[srj19]

I can think of some really inefficient ways to do this, but I'm hoping someone can help. My experience has been with C based langues like Java so I'm a little rusty on VBS.

I need to capture a list of unique usernames appearing in a column on the sheet, and also the number of times each user has a desired value occur for their row (an error). The number of users and the names of the users will change from one sheet to the next making a static solution unworkable. The user names will be unsorted in the column.

My first reflex is to use an array, and an associative array if possible would allow me to reference the user by name, instead of a clumsier index number and iterating through a loop to verify if the user is already stored in the array. I don't know if it's possible in VBS though. I'll add any new users names to this array if they aren't already in there.

Any suggestions?
Thanks,

Scott

 

[Norie]

Scott

What are you trying to do?

Do you just want a list of unique names?

If so then perhaps Data>Advanced filter... might work using the unique items option.

PS I don't think Java is a C based language.

 

[jindon]

try

Sub test()
Dim a, b(), i As Long, n As Long
a = Range("a1", Range("a" & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a,1), 1 To 2)
With CreateObject("Scripting.Dictionary")
     .CompareMode = vbTextCompare
     For i = 1 To UBound(a,1)
          If Not .exists(a(i,1)) Then
               n = n + 1 : b(n,1) = a(i,1)
              .add a(i,1) , n
          End If
          If IsError(a(i,2)) Then b(.item(a(i,1)),2) = b(.item(a(i,1)),2) + 1
     Next
End With
Range("c1").Resize(n,2).Value = b
End Sub

 

[srj19]

Thanks for the help, I have yet to try the code offered, but will in the morning.

I tried to be as concise as possible with what I was trying to do. I have a report containing results from 8-10 different persons that will change from month to month. I need to first create a list or array that contains each person appearing within the sheet, and then track an error count for each person (with errors being indicated in another field on the same row)

I think I can do this the hard way, iterating through indexes of an array of users, checking it the user exists in the array, adding them if they do not, etc. But I'm hopeing for an elegant way.

Scott

 

[Stormseed]

Re: Stuck on this: building array of only unique column valu

I can think of some really inefficient ways to do this, but I'm hoping someone can help. My experience has been with C based langues like Java so I'm a little rusty on VBS.
Scott

First, that thing you mentioned "VBS" is not used in here. I think you are referring to VB Scripts.

Also, wat has java got to do with VBA? Only the principles of C++ and not C are adopted by Java and its a completely different language. Syntax is somewat similar but both of them are two ends of the same river

 

[srj19]

I know I'm among the technically minded when we're debating the history and origin of a programming language.

Maybe "C based" was the wrong way to put it. "C syntax" I think is where I was comming from. I can program excel macros due to my prior programming experience, but honestly I can't tell you what language EXACTLY I've been using, I thought it was Visual Basic Script (VBS?)

Back to my challenge, anyone have an idea to:grab the unique values appearing in a column (persons), and then track the number of times each unique person has a value of 1 in a cell in that person's row.

That's it in a nutsell,

Scott

 

[Norie]

Scott

Did you try what jindon posted?

That's VBA (Visual Basic for Applications) code.

 

[srj19]

I think I may have found what I'm looking for, it does rely on looping through the array in order to determine if the person has already been added to the array, but I guess that will work.




Option Base 1

Function UniqueItems(ArrayIn, Optional Count As Variant) As Variant
' Accepts an array or range as input
' If Count = True or is missing, the function returns the number
' of unique elements
' If Count = False, the function returns a variant array of unique
' elements

Dim Unique() As Variant ' array that holds the unique items
Dim Element As Variant
Dim i As Integer
Dim FoundMatch As Boolean

' If 2nd argument is missing, assign default value
If IsMissing(Count) Then Count = True

' Counter for number of unique elements
NumUnique = 0

' Loop thru the input array
For Each Element In ArrayIn
FoundMatch = False

' Has item been added yet?
For i = 1 To NumUnique
If Element = Unique(i) Then
FoundMatch = True
GoTo AddItem '(Exit For-Next loop)
End If
Next i

AddItem:
' If not in list, add the item to unique list
If Not FoundMatch Then
NumUnique = NumUnique + 1
ReDim Preserve Unique(NumUnique)
Unique(NumUnique) = Element
End If

Next Element

' Assign a value to the function
If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
End Function

 

[srj19]

I think I may have found what I'm looking for, it does rely on looping through the array in order to determine if the person has already been added to the array, but I guess that will work.




Option Base 1

Function UniqueItems(ArrayIn, Optional Count As Variant) As Variant
' Accepts an array or range as input
' If Count = True or is missing, the function returns the number
' of unique elements
' If Count = False, the function returns a variant array of unique
' elements

Dim Unique() As Variant ' array that holds the unique items
Dim Element As Variant
Dim i As Integer
Dim FoundMatch As Boolean

' If 2nd argument is missing, assign default value
If IsMissing(Count) Then Count = True

' Counter for number of unique elements
NumUnique = 0

' Loop thru the input array
For Each Element In ArrayIn
FoundMatch = False

' Has item been added yet?
For i = 1 To NumUnique
If Element = Unique(i) Then
FoundMatch = True
GoTo AddItem '(Exit For-Next loop)
End If
Next i

AddItem:
' If not in list, add the item to unique list
If Not FoundMatch Then
NumUnique = NumUnique + 1
ReDim Preserve Unique(NumUnique)
Unique(NumUnique) = Element
End If

Next Element

' Assign a value to the function
If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
End Function

 

[srj19]

Wow the code Jindon posted looks interesting, I have to confess that I'm not at the level where I can follow it that well though. It's not clear to me what is going on at certain points of what the end result it.