List of Unique Data from Left portion of Range

[spacecaptainsuperguy]

I have a list of accounts structured as "Entity"-"Account#" with a dash in the middle such as ABC-123. The data on the left of the dash can be variable in length and contain both letters and numbers.

I'd like to extract a list of unique Entities (i.e. the left side of the dash). I was able to find the following code online which gets me close:

Sub GetUnique_Collection() 'Using the Collection object
'
    Dim SourceRng As Range
    Dim UniqColl As New Collection
    Set SourceRng = Range("A2:A30")
    On Error Resume Next
    For Each cell In SourceRng.Cells
        UniqColl.Add cell.Value, cell.Value
    Next
    On Error GoTo 0
    ReDim UniqArray(1 To UniqColl.Count)
    For i = 1 To UniqColl.Count
        UniqArray(i) = UniqColl(i)
    Next
    'Optional sort routine can be inserted here
        Range("H1").Resize(UniqColl.Count, 1).Value = WorksheetFunction.Transpose(UniqArray)
        
End Sub

The catch is this code only works if the list had all the account numbers already removed. Rather than stripping off the account numbers and creating a whole new list to work from, I would like to try to build into this code to have it check the data left of the dash to see if it is unique or not. I'm guessing it would be this line of code that would need to be changed to do that?
UniqColl.Add cell.Value, cell.Value
But have no idea how to go about writing that out other than the formula to do so.

Any thoughts are much appreciated.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your xl version this could easily be done with a formula.

 

[Fluff]

If your happy with a formula, how about

=UNIQUE(TEXTBEFORE(FILTER(A2:A100,A2:A100<>""),"-"))

 

[spacecaptainsuperguy]

Done. Thanks for the tip.
Using Excel 365.
Reason I'm trying to squeeze this into a macro is that it is part of a bigger macro project, so the more of it I can automate the better.

 

[Fluff]

Ok, does the formula in post#3 work?

 

[spacecaptainsuperguy]

Ok, does the formula in post#3 work?

No. I get an #N/A
Edit. Scratch that. Using it on the wrong data set. Let me check

 

[spacecaptainsuperguy]

Yes it does work

 

[Fluff]

Ok, how about

Sub spacecaptain()
   Dim Ary As Variant
   Ary = Evaluate("UNIQUE(TEXTBEFORE(FILTER(A2:A100,A2:A100<>""""),""-""))")
   Range("H1").Resize(UBound(Ary)).Value = Ary
End Sub

 

[spacecaptainsuperguy]

Ok, how about

Sub spacecaptain()
   Dim Ary As Variant
   Ary = Evaluate("UNIQUE(TEXTBEFORE(FILTER(A2:A100,A2:A100<>""""),""-""))")
   Range("H1").Resize(UBound(Ary)).Value = Ary
End Sub

Like a charm.
Awesome. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.