Create list of unique values from a range in Excel

Question

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.

If it isn't too hard to figure out, sure! I have a little bit of VBA experience. — Dom Vito, Nov 19 '18 at 17:51
https://stackoverflow.com/questions/5890257/populate-unique-values-into-a-vba-array-from-excel/5891317#5891317 — Tim Williams, Nov 19 '18 at 18:01

score 2 · Accepted Answer · answered Nov 19 '18 at 18:10

2

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

answered Nov 19 '18 at 18:10

Gary's Student

95,722
10
59
99

Brilliant. Thank you! – Dom Vito Nov 19 '18 at 19:29

score 1 · Answer 2 · answered Nov 19 '18 at 18:07

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.

Create list of unique values from a range in Excel

2 Answers2