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!

 

[Artik]

Try:

Sub OnlyUniques()
    Dim rngS As Range
    Dim v As Variant
    Dim vS As Variant
    
  Dim oDic As Object

  Set oDic = CreateObject("Scripting.Dictionary")
    Set rngS = Range("A2").CurrentRegion
    
    With rngS
        Set rngS = .Offset(1).Resize(.Rows.Count - 1)
    End With
    
    vS = rngS.Value
    
    On Error Resume Next
    For Each v In vS
        If Len(v) > 0 Then
            oDic.Add v, 0
        End If
    Next v
    
    If oDic.Count > 0 Then
        v = oDic.Keys()
        v = Application.Transpose(v)
        
        With rngS.Offset(rngS.Rows.Count + 3).Cells(1).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]

Thank you for the quick reply!

My tiny brain barely comprehends what's happening here and I'm having some issues that I can't seem to correct:
Originally, I set rngS to BA16:BR21 where I have the numbers stored which is the same range as CurrentRegion but that only netted me two results instead of five. So, I adjusted rngS to Range("BA16").CurrentRegion and that gave me the proper five results but now included the row headers too. My headers are in row 15 so I'm not clear why they were included if CurrentRegion is selecting only the numbers.

Also, how can I have the results land one column right of the current region instead of below it? I tried fussing around with ".Offset(rngS.Rows.Count + 3).Cells(1).Resize(UBound(v))" to "rngS.Columns.Count + 2" but that moved the results even lower instead of right. If the CurrentRegion ends in column BR, I would like for the results to land in BT16.

 

[Artik]

My tiny brain barely comprehends what's happening here and I'm having some issues that I can't seem to correct:

Seeing the data image, I assumed that the headers of the table are in row 1, that is, that the visible range of data covers the range A1:R7. Set rngS = Range("A2").CurrentRegion will return us the range A1:R7, even though the base cell is the cell in row 2. In the With...End With construct, I move the range A1:R7 by 1 row, that is, I get A2:R8, and then reduce the range by 1 row getting A2:R7.

Also, how can I have the results land one column right of the current region instead of below it? I tried fussing around with ".Offset(rngS.Rows.Count + 3).Cells(1).Resize(UBound(v))" to "rngS.Columns.Count + 2" but that moved the results even lower instead of right. If the CurrentRegion ends in column BR, I would like for the results to land in BT16.

Let me explain this construction:
With rngS.Offset(rngS.Rows.Count + 3).Cells(1).Resize(UBound(v))

The range A2:R7 is shifted by the number of rows of this range + 3, so I get A11:R16. Then from the new range I take only the first cell and expand the range downward by the number of elements of the array v (that is, by 5), I get range A11:A15, into which I paste the values from the array. In the line .Offset(-1).Cells(1).Value = "Found RA's" from the new range A11:R16, I take the first cell (A11) and move it 1 row higher, i.e. A10, into which I insert "Found RA's".
The Offset and Resize properties each have two optional parameters. The first is for rows, the second is for columns. Note that in Offset 1 means moving 1 from the base cell, so Range("A1").Offset(1) gives us a reference to A2, while in Resize 1 means expanding by 1, but including the base cell. That is, Range("A1").Resize(1) refers to A1. Please read in the Help on Resize and Offset
With the above knowledge, try writing a code snippet yourself about displaying the results on the right side of the source table. Show the code to check.

Artik

 

[Lil Stinker]

Thank you! The explanation helps. I'm more of a visual learner so the technical side of things gets jumbled. While I understand what you're saying, I don't fully comprehend it until I can see the actual process and unfortunately, with this code, using Step Thru doesn't show anything but the end result. While I understand the concept of Offset and Resize, I couldn't actually see what it was doing to fully grasp it.

I was able to get the proper results where I want them displayed by making the following modifications:

Set rngS = CHKINList.Range("BA16").CurrentRegion

With rngS
   Set rngS = .Offset(2).Resize(.Rows.Count)
End With

... & ...

'place result range one column over from RA range
   With rngS.Offset(, 19).Cells(1).Resize(UBound(v))
      .Value = v
      .Sort Key1:=.Cells(1), Order1:=xlAscending
      .Offset(-1).Cells(1).Value = "Found RA's"
   End With

I had to Offset(2) because as it turns out, there are values in BA14 and BA15. While BA15 contains the header, there is another line above BA15 which does not pertain to this range. So I figure when you Offset the CurrentRegion, it continues to grab anything that has a value? I haven't used CurrentRegion before mainly because my ranges are usually fixed. I'm guessing you chose to use CurrentRegion along with Offset and Resize assuming the range size may fluctuate? Because now I am able to get the same results by setting a fixed range and removing the With Offset/Resize construct and it still appears to work:

Set rngS = CHKINList.Range("BA16:BR21")

'With rngS
'   Set rngS = .Offset(2).Resize(.Rows.Count)
'End With

 

[Artik]

Set rngS = CHKINList.Range("BA16").CurrentRegion

With rngS
   Set rngS = .Offset(2).Resize(.Rows.Count)
End With

Range("BA16").CurrentRegion will return the range BA14:BR21. Set rngS = .Offset(2).Resize(.Rows.Count) will move this range two rows down, so you get BA16:BR23. That is, you have a redundancy of two lines BA22:BR23. .Resize(.Rows.Count) won't change anything because you calculated the same number of rows. Rather, it should be:

Set rngS = CHKINList.Range("BA16").CurrentRegion

With rngS
   Set rngS = .Offset(2).Resize(.Rows.Count - 2)
End With

You will get a range BA16:BR21.

Other than that, it seems to be OK. I think you already understand the use of Offset and Resize.

Artik

 

[Lil Stinker]

Great, thanks for the help!

 

[Artik]

To obtain unique values quite often a Dictionary is used. The keys of the Dictionary must be unique. By running through the elements of the vS array and ignoring errors (On Error Resume Next), we will get unique values in the dictionary. Then we copy the keys to the array v. Since the array v will have a horizontal "orientation" we must transpose it to get a "vertical" array.

Below is a version of the code for the visual learner. Use the F5 key at each Stop. Observe the highlighted areas at each Stop.

Sub OnlyUniques_3()
    Dim rngS As Range
    Dim v As Variant
    Dim vS As Variant
    
  Dim oDic As Object

  Set oDic = CreateObject("Scripting.Dictionary")
    Set rngS = Range("BA16").CurrentRegion
    
    rngS.Select
    Stop
    
    With rngS
        .Offset(2).Select
        Stop
        Set rngS = .Offset(2).Resize(.Rows.Count - 2)
    End With
    
    rngS.Select
    Stop
    
    vS = rngS.Value
    
    On Error Resume Next
    For Each v In vS
        If Len(v) > 0 Then
            oDic.Add v, 0
        End If
    Next v
    
    If oDic.Count > 0 Then
        v = oDic.Keys()
        v = Application.Transpose(v)
        
        rngS.Offset(, rngS.Columns.Count + 1).Select
        Stop
        rngS.Offset(, rngS.Columns.Count + 1).Cells(1).Select
        Stop
        rngS.Offset(, rngS.Columns.Count + 1).Cells(1).Resize(UBound(v)).Select
        Stop
        
        With rngS.Offset(, rngS.Columns.Count + 1).Cells(1).Resize(UBound(v))
            .Value = v
            .Sort Key1:=.Cells(1), Order1:=xlAscending
            
            .Offset(-1).Cells(1).Select
            Stop
            .Offset(-1).Cells(1).Value = "Found RA's"
        End With
    End If
    
End Sub

Artik

 

[Lil Stinker]

Oh wow! That is quite helpful! Thank you so much! Is there any way to peak into the Dictionary? That's another aspect that's alluded my understanding.

 

[Artik]

In the Watches window (from the menu, choose View/Watch window), you can preview the values of variables as the code is executed in step mode. Add oDic.Keys() in this window (from the context menu in the Watches window, select Add Watch), because we are only interested in the Dictionary's keys. To see when a value is added to a key, and when it is ignored (i.e. the key already exists), change this code fragment

(...)
    vS = rngS.Value
    
    On Error Resume Next
    For Each v In vS
        If Len(v) > 0 Then
            oDic.Add v, 0
        End If
    Next v
    
    If oDic.Count > 0 Then
(...)

to

(...)
    vS = rngS.Value

    On Error Resume Next
    For Each v In vS
        Err.Clear

        If Len(v) > 0 Then
            oDic.Add v, 0
            If Err.Number = 0 Then
                MsgBox "Added a new key"
            Else
                MsgBox "Duplicate"
            End If
        End If
        Stop
    Next v

    If oDic.Count > 0 Then
(...)

Artik