Compile a list of distinct values from a name range that spans multiple columns

Question

I'm working on trying to compile a list of distinct values from a named range 'WESupplierALL' into a single column. I've tried the Unique and advanced filter functions but i can seem to crack it. Any ideas? I'm open to all options including VBA, Formulas etc.

Check this article here in stackoverflow. [Excel UNIQUE Across Columns](https://stackoverflow.com/a/66361639/5514747) — Harun24hr, May 24 '21 at 10:11
Does this answer your question? [Excel VBA Dictionary Storing and Retrieving](https://stackoverflow.com/questions/45738452/excel-vba-dictionary-storing-and-retrieving) - The [Dictionary object](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object) in VBA can be used to store unique key's (values) which are useful for finding 1 instance of duplicated data in a data set. — Samuel Everson, May 24 '21 at 10:33
Do you just want a list the unique values from , from the pic above, ```A2:M10```? — Chris H., May 24 '21 at 12:53

score 0 · Answer 1 · answered May 24 '21 at 13:12

As suggested in the comments (linked) you can use a combination of TEXTJOIN and FILTERXML but the formulas are quite long which impacts readability. Power Query is another option.

Over the years, I used my own VBA function to unite values. You can use the DM_ARRAY function from my repository: VBA-ArrayTools. There is a demo workbook available and you will find a dedicated worksheet for each User Defined Function (UDF). For DM_ARRAY, the first parameter is the number of columns you want as output and then you can pass as many ranges as you need. In your case you could achieve the desired result with =UNIQUE(DM_ARRAY(1,E2:E23,I2:I23,M2:M23)). Of course you can pass a named range instead like =UNIQUE(DM_ARRAY(1,WESupplierALL)).

Moreover, if you want to achieve the same result on a different computer where you don't have the advanced array formulas, then the same library has a DM_UNIQUE function that you can use.

VBasic2008 · Answer 2 · 2021-05-24T14:06:07.193

Write Unique Values From Multiple to One Column

Adjust (play with) the values in the constants section and the workbook.

Option Explicit

Sub writeUnique()
    
    Const wsName As String = "Sheet1"
    Const ColsList As String = "E,I,M"
    Const fRow As Long = 2
    Const lRow As Long = 23
    Const dFirst As String = "A2"
        
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim Cols() As String: Cols = Split(ColsList, ",")
    Dim rCount As Long: rCount = lRow - fRow + 1
    
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' ignore case
      
    Dim crg As Range
    Dim Data As Variant
    Dim Key As Variant
    Dim r As Long, n As Long
    
    For n = 0 To UBound(Cols)
        Set crg = ws.Columns(Cols(n)).Resize(rCount).Offset(fRow - 1)
        Data = crg.Value
        For r = 1 To rCount
            Key = Data(r, 1)
            If Not IsError(Key) Then ' ignore error values
                If Len(Key) > 0 Then ' ignore blanks
                    dict(Key) = Empty
                End If
            End If
        Next r
    Next n
    
    If dict.Count = 0 Then Exit Sub
    
    ReDim Data(1 To dict.Count, 1 To 1)
    r = 0
    
    For Each Key In dict.Keys
        r = r + 1
        Data(r, 1) = Key
    Next Key
    
    With ws.Range(dFirst)
        .Resize(r).Value = Data
        .Resize(.Worksheet.Rows.Count - .Row - r + 1).Offset(r).ClearContents
    End With
    
End Sub

Compile a list of distinct values from a name range that spans multiple columns

2 Answers2

Write Unique Values From Multiple to One Column