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

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

ColinAttachment 29289

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.

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

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

Code:

---

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

---

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

Code:

---

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

---

here is a macro enabled excel.

Wow - That is pure genius!

Thank you!

:friends: