CODEWITHSUNDEEP
excelexcel-formula
Charbel
Charbel

Reputation: 14687

excel unique values

I've seen many answers to this questions on the internet (and on stackoverflow) something is not working when I try the solution that everyone seems to suggest.

Basically I have a list of values (say in row 24, column C to U), and I'd like to create a (range) formula in row 25, that would give me the distinct values of the values in the range C24:U24.

I used the formula below

=INDEX($C$24:$U24,MATCH(0,COUNTIF($B$25:B25,$C$24:$U$24),0))

It is not working, it is just returning the 1st item of the list.

Upvotes: 0

Views: 1923

Answers (2)

Gaijinhunter
Gaijinhunter

Reputation: 14685

I know you didn't ask for a VBA function, but in case some readers out there want to know how to do this via VBA, here is the function. It takes a range of cells and creates a string of the unique elements only. You can add a seperator as well (like if you want them comma-seperated).

Function UniqueList(ByVal cell_range As range, _
                    Optional ByVal seperator As String = "") As String

Dim vArray As Variant
Dim result As String
Dim i As Long, j As Long
Dim v As Variant
Dim dictionary As Object
Set dictionary = CreateObject("scripting.dictionary")

vArray = cell_range.Value

For i = 1 To UBound(vArray, 1)
    For j = 1 To UBound(vArray, 2)
        If Len(vArray(i, j)) <> 0 Then
            dictionary(vArray(i, j)) = 1
        End If
    Next
Next

For Each v In dictionary
    result = result & (seperator & v)
Next

If Len(result) <> 0 Then
    result = Right$(result, Len(result) - Len(seperator))
End If

UniqueList = result

End Function

For those who care: It dumps the cell values into a variant array, then populates a dictionary with each one (except blank cells). Since dictionaries only hold unqiue keys, it weeds out the dupes. Then I loop through the dictionary and create a string with each entry. I clean up the extra seperator at the end so the list is clean.

Upvotes: 2

JMax
JMax

Reputation: 26591

You still have to copy-paste the formula in several cells. In your example, $B$25 would be the first cell (because of this part of the formula: $B$25:B25). When you drag this formula down, it will check if the value is already in $B$25:B26 and so on.

Upvotes: 3

Related Questions