Copy unique values from new data and append to existing list in another worksheet

[colindickson]

Hi,

Please find attached an example of what i'm trying to achieve...

Basically I load data into the data tab, which will have more rows of data - what i'd like to do is filter by unique values then copy and paste those values into the other worksheet "Unique_Values"

Hopefully the attached explains it better.

Thanks in advance

ColinTest.xlsx

[arnelgp]

you can do it manually.
copy range A:A, to new sheet.
select the Range you copied.
on the ribbon (Data->Remove duplicate).
exercise is good for the heart.

[colindickson]

Thanks arnelgp, however I need the list in Unique_List to remain static as this I will also be adding data to columns (hard pasting the previous column) so I need the historical data in columns B onwards - sorry that's not in my example...

New example attached....

In affect, it's unique values based on unique list vs the new data in data tab

[colindickson]

Think i'm getting fairly close with the code below, but I need it to append it to the other worksheet?

Sub Unique()   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      Range("C2").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

[colindickson]

Getting closer...... any help much appreciated:

Sub Mallesh()

   Dim Cl As Range
   Dim LastRow As Long
   
   Sheets("Unique_List").Select
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
    Sheets("Data").Select
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      
    Sheets("Unique_List").Select
    LastRow = Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("A" & LastRow)
      .Copy .Offset(1)
      .Value = Application.Transpose(.Keys)
      
      End With
      
   End With
End Sub

[arnelgp]

here is a macro enabled excel.

[colindickson]

Wow - That is pure genius!

Thank you!

[arnelgp]