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

Colin29289

[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]

:friends: