# List of Unique Data from Left portion of Range



## spacecaptainsuperguy (Dec 22, 2022)

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 (Dec 22, 2022)

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 (Dec 22, 2022)

If your happy with a formula, how about
	
	
	
	
	
	



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


----------



## spacecaptainsuperguy (Dec 22, 2022)

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 (Dec 22, 2022)

Ok, does the formula in post#3 work?


----------



## spacecaptainsuperguy (Dec 22, 2022)

Fluff said:


> 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 (Dec 22, 2022)

Yes it does work


----------



## Fluff (Dec 22, 2022)

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 (Dec 22, 2022)

Fluff said:


> Ok, how about
> 
> 
> 
> ...


Like a charm.
Awesome. Thank you so much!


----------



## Fluff (Dec 22, 2022)

You're welcome & thanks for the feedback.


----------

