Removing duplicates for left to right formatted data

[Lil Stinker]

Can anyone tell me if there is a way in Excel 2019 to take data that is formatted left to right like this...

Main														Alternative		Missing
10089	10089	10090	10090	10090	10090	10090	10086	10086	10086	10086	10086	10086	10086	10088	10088	10086	10086
10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086
10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086
10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086
10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10054	10054
10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086	10086

and remove all the duplicates to just give me this...?

Found RA's
10054
10086
10088
10089
10090

Preferably via a formula or VBA? Remove Duplicates and Consolidate don't seem to work with this formatting.

Much obliged!

 

[Lil Stinker]

Hello again-
Your explanations are very helpful. However, I've had to make some design changes to my form and I'm not able to figure out how to adjust the code to the new ranges. I'm wondering how to make this work for two column ranges that are set apart rather than the horizontal layout I started with.

Is it still possible to take the numbers that appear in Range 1 ("C15:C45") and Range 2 ("M15:M45") and consolidate them to show only the unique values in Column W?

I've been able to get one range to work but not both. I've included screenshot for reference.

 

[Artik]

This time we are running over the cells of the defined rngS range, instead of the elements of the array, as before. With such a small range, you won't see a difference in speed.
I assumed this time that the ranges are fixed, which simplifies the code.

Sub OnlyUniques_1()
    Dim rngS As Range
    Dim rng As Range
    Dim v As Variant

    Dim oDic As Object

    Set oDic = CreateObject("Scripting.Dictionary")

    Set rngS = Union(Range("C15:C45"), Range("M15:M45"))

    On Error Resume Next

    For Each rng In rngS
        If Len(rng.Value) > 0 Then
            oDic.Add rng.Value, 0
        End If
    Next rng

    If oDic.Count > 0 Then
        v = oDic.Keys()
        v = Application.Transpose(v)

        With Range("U40").Resize(UBound(v))
            .Value = v
            .Sort Key1:=.Cells(1), Order1:=xlAscending
            .Offset(-1).Cells(1).Value = "Found RA's"
        End With
    End If

End Sub

Artik

 

[Lil Stinker]

Thanks for the reply! It works perfectly.

I had the right idea in mind just didn't know how to execute it in VBA. For instance, I didn't know you needed Union to combine different ranges. I hadn't changed the For Each loop from the variants to ranges either but that makes perfect sense too. Though I am still perplexed as to why Transpose is still needed when the ranges and results are both laid out vertically in columns. When I had this commented out, I only saw one result duplicated seven times instead of seven different results.

 

[Artik]

The variable v has little to do with cell ranges. Instead, it has a significant relationship with the Dictionary. Please read again the beginning of post #8. The capabilities of the Dictionary are very well described with examples by Paul Kelly on his web site.

Artik

 

[Lil Stinker]

I had assumed the Dictionary array was horizontal because the original array was horizontal in post #8. With the new layout being vertical ranges, I was assuming the Dictionary array would also be vertical. I have used Dictionary before without needing to transpose the results.

At any rate, thank you for your help. I'll go back and review Paul's Dictionary walkthroughs. Personally, a lot of what Paul covers is over my head.