If any specific #s are present in range, show each in another cell

[Steve]

If I have a range, e.g. A1:A10, and in that range are various #'s, such as
1s, 2s, 3s or 4s. I'd like a formula in cell B1 that would produce all the
DIFFERNT #'s that are in that range. i.e.

If A1:A10 has only 1s in it, I'd like 1 in cell B1
If A1:A10 has 1s and 2s in it, I'd like 1,2 in cell B1
If A1:A10 has 1s,2s, and 3s in it, I'd like 1,2,3 in cell B1
If A1:A10 has 1s,2s, 3s, and 4s in it, I'd like 1,2,3,4 in cell B1
etc.
The number of times each # is in the range is irrelevent.

Thanks,

Steve

 

[Per Jessen]

Hi Steve

This UDF should do it:

Public Function UniqueNumbers(InputRange As Range) As Variant
Dim Unique() As Variant
Dim IsUnique As Boolean
ReDim Unique(0)

For Each c In InputRange.Cells
If WorksheetFunction.CountIf(InputRange, c) >= 1 Then
If Unique(0) = "" Then
Unique(0) = c.Value
Else
For v = LBound(Unique) To UBound(Unique)
If Unique(v) = c.Value Then
IsUnique = False
End If
Next
If IsUnique Then
ReDim Preserve Unique(UBound(Unique) + 1)
Unique(UBound(Unique)) = c.Value
End If
IsUnique = True
End If
End If
Next

UniqueNumbers = Unique(0)
For c = 1 To UBound(Unique)
UniqueNumbers = UniqueNumbers & ", " & Unique(c)
Next
If UniqueNumbers = "" Then
UniqueNumbers = CVErr(xlErrNA)
End If
End Function

Regards,
Per

 

[Steve]

I'm sorry, but I've never used a UDF before, and have no idea how to even
begin with this. Does this go into the cell ?

 

[Per Jessen]

Steve,

An UDF is an userdefined function, ie a macro. To use it you have to insert
it in the VBA editor. Here's what to do:

Open the VBA editor (ALT+F11)
Goto Insert > Module
Paste the code i posted to you earlier in the codesheet which appear
Close the VBA editor.

Now you are ready to use the UDF. To call the function select the desired
cell, and type:

=UniqueNumbers(A1:A10)

Change the range in the paranthesis as needed and press enter.

Hopes this helps.

 

[Steve]

Amazing. This is working perfectly. Thank you so much. And thanks for the
addional primer on UDFs.
One more question, and not important at all, but, is there a way to have the
numbers returned be in ascending order ? E.g. I guess they're returning as
they're found in the range, such as 2100, 2300, 2130, 2200, which in my
range, the 2300 happens to be before the first 2130 number. Again, not really
needed, just wondering if it can be made to return the numbers in ascending
order.

Thanks again,

Steve

 

[Per Jessen]

Thanks for your reply, I am glad you made it work.

To return the list sorted ascending, replace the existing code with the code
below:

Public Function UniqueNumbers(InputRange As Range) As Variant
Dim Unique() As Variant
Dim SortedUnique() As Integer
Dim IsUnique As Boolean
Dim Counter As Variant
ReDim Unique(0)

IsUnique = True
For Each c In InputRange.Cells
If WorksheetFunction.CountIf(InputRange, c) >= 1 Then
If Unique(0) = "" Then
Unique(0) = c.Value
Else
For V = LBound(Unique) To UBound(Unique)
If Unique(V) = c.Value Then
IsUnique = False
End If
Next
If IsUnique Then
ReDim Preserve Unique(UBound(Unique) + 1)
Unique(UBound(Unique)) = c.Value
End If
IsUnique = True
End If
End If
Next
ReDim Preserve SortedUnique(UBound(Unique))

For c = o To UBound(Unique)
SortedUnique(c) = WorksheetFunction.Min(Unique)
For r = 0 To UBound(Unique)
If Unique(r) = SortedUnique(c) Then
Unique(r) = ""
Exit For
End If
Next
Next

UniqueNumbers = SortedUnique(0)
For c = 1 To UBound(Unique)
UniqueNumbers = UniqueNumbers & ", " & SortedUnique(c)
Next
If UniqueNumbers = "" Then
UniqueNumbers = CVErr(xlErrNA)
End If
End Function

Best regards,
Per

 

[Steve]

Thank you again so much. This is even more perfect.

Much appreciated.

Steve