Unique value list from 5 different non-adjacent columns

[Legacy 287389]

hi good people,

I amsearching but appear to only find help with text in different columns...I have numbers in 5 non-adjacent columns and need a unique list..please can someone assist with a formula, or maybe an easier route which would be VBA?..columns are: E, H, L, P and S..thank you all very much!!

sorry I just remembered, I do not want the list, just a count of how many unique numbers there are...thanx guys!!

 

[mumps]

Try this macro. I've used columns A, E and F. You will have to modify the line of code where indicated to suit your needs.

Sub CountUnique()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    Dim count As Long
    Dim colRng As Range
    Set colRng = Application.Union(Range("A1:A" & Range("A" & Rows.count).End(xlUp).Row), Range("E1:E" & Range("E" & Rows.count).End(xlUp).Row), Range("H1:H" & Range("H" & Rows.count).End(xlUp).Row)) 'modify ranges to suit your needs
    Dim rng As Range
    For Each rng In colRng.Cells
         If Not RngList.Exists(rng.Value) Then
            RngList.Add rng.Value, 0
        End If
    Next
    count = RngList.count
    MsgBox count
    Application.ScreenUpdating = True
End Sub

If each column of data is the same length, you can use this slightly simpler version:

Sub CountUnique()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    Dim count As Long
    Dim colRng As Range
    Set colRng = Application.Union(Range("A1:A" & LastRow), Range("E1:E" & LastRow), Range("H1:H" & LastRow))  'modify ranges to suit your needs
    Dim rng As Range
    For Each rng In colRng.Cells
         If Not RngList.Exists(rng.Value) Then
            RngList.Add rng.Value, 0
        End If
    Next
    count = RngList.count
    MsgBox count
    Application.ScreenUpdating = True
End Sub

 

[Legacy 287389]

Good morning mumps!!!,

Excellent work!..thank you very very much!!, your assistance and time really is appreciated!..thank you kindly..

 

[mumps]

You are very welcome.