CODEWITHSUNDEEP
excelexcel-formulaexcel-2010array-formulas
Dom Vito
Dom Vito

Reputation: 567

Create list of unique values from a range in Excel

I have data (names) spread out in a sheet in Excel. It is in columns A through M. Some rows have data in every column (A-M), some rows have nothing.

I want to extract a list of every unique value (or even a list of every value, and then I can remove duplicates) in the range.

For the life of me, I cannot figure out how to do this and haven't found an example online that pulls unique values from a RANGE, and not a single column.

Any ideas? Let me know if you need a screenshot of the data for more context.

Upvotes: 0

Views: 1338

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Give this macro a try:

Sub MAINevent()
    Dim it As Range, r As Range, x0
        With CreateObject("scripting.dictionary")
            For Each it In Range("A:M").SpecialCells(2)
                x0 = .Item(it.Value)
            Next

            Set r = Cells(1, "N").Resize(.Count, 1)
            r.Value = Application.Transpose(.Keys)
        End With
End Sub

enter image description here

Upvotes: 2

toroc
toroc

Reputation: 126

You can use Go to Special (Ctrl+G -> Special) to select blanks and remove them (Ctrl+- with cells selected). Then use the formula described here to stack all your columns in one, on top of each other. Then replace formulas with values and finally, use Remove Duplicates.

Upvotes: 1

Related Questions