If any specific #s are present in range, show each in another cell
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" skrev i meddelelsen
...
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
|