List of values within a range

**riwiseuse** · 09-30-2010, 09:09 AM

Tried searching on "List" but haven't found an answer to this query. Doesn't really seem to overlap the LIST function.

Have records with non-consecutive numerical designations in one column. The numercial designations repeat so that for 10,000 records there might be 7 or 8 values reflected in that column. I can scroll through and jot down the 7 or 8 but is there a function that can query a range and return a comma separated list or a table of the distinct values in that range.

So if the column looked like this:

8
8
8
8
11
11
11
11

the function would return 8,11.

Thanks,

Brian

**Bob Phillips** · 09-30-2010, 09:36 AM

I would create a simple uDF

Public Function GetUniques(rng As Range) As String
Dim coll As Collection
Dim itm As Variant
Dim tmp As String
Dim LastRow As Long
Dim i As Long

    With rng
    
        Set coll = New Collection
        
        LastRow = .Cells(.Parent.Rows.Count, .Cells(1, 1).Column).End(xlUp)
        On Error Resume Next
        For i = 1 To LastRow
    
            If .Parent.Cells(i, .Cells(1, 1).Column).Value2 <> "" Then
            
                coll.Add CStr(.Parent.Cells(i, .Cells(1, 1).Column).Value2), CStr(.Parent.Cells(i, .Cells(1, 1).Column).Value2)
            End If
        Next i
        On Error GoTo 0
        
        For Each itm In coll
        
            tmp = tmp & itm & ","
        Next itm
        
        GetUniques = Left$(tmp, Len(tmp) - 1)
    End With
                
End Function

and use that like so

=GetUniques(A1:A200)

**riwiseuse** · 10-01-2010, 06:19 PM

[QUOTE=Bob Phillips;2391821]I would create a simple uDF

[code]

Bob,

must concede that the uDF thing is not too transparent for me. I used to make functions and macros in separate sheets writing the code in cells but ever since the Visual Basic Editor, I can't figure out how to make a function.

It was probably supposed to make things better, but I'm lost. Can't find how you name the project or make it a function. It wasn't that that was so obvious with the old system but I had figured it out.

Then I'd love to understand how your func tion actually accomplishes the goal.

Meantime I came up with a workaround. I sorted the data by precinct number and I inserted a column next to the precinct column and if the precinct column were A, put in this formula in B2: =IF(A2=A3,0,A2) and then filled that formula down the entire column. So this will only put a value in the B column if the value in the next row is not the same. Then I sort on the B column and get a sorted list of precincts.

Thanks for taking the time to respond,

Brian

**TMS** · 10-01-2010, 06:42 PM

If you're OK with doing it manually, much quicker is to use an Advanced Filter and select unique entries and write it to a new area.

See the attached picture.

Regards

**riwiseuse** · 10-04-2010, 09:44 AM

TM - Who Knew. That advanced filter function is just what I was trying to accomplish. Thanks mucho.

Bob, still glad of any feedback on the structure of your uDF and pointer to a primer on how to actually create, name and call uDFs easily.

Thanks all, marking this solved.

Brian