Creating one complete list from two partials

[RichardMGreen]

Hi all

I have two separate lists I need to amalgamate into one.
Some of the items will appear in both lists, so I'll only want that item to appear once (hope that makes sense).

Here's some sample data.
List 1:
C84004
C84011
C84034
C84039
C84043
C84060
C84063
C84064
C84072


List 2:
C81026
C84004
C84011
C84018
C84023
C84034
C84039
C84043
C84046
C84060


The final list should be:
C81026
C84004
C84011
C84018
C84023
C84034
C84039
C84043
C84046
C84060
C84063
C84064
C84072


I'll need some sort of VBA/formula to do this automatically if at all possible.

TIA

 

[Andrew Poulsom]

Combine the lists into one list using Copy/Paste. Then use Advanced Filter to extract the unique items.

 

[RichardMGreen]

Any ideas on how to do that automatically?

 

[JackDanIce]

And to expand on Andrew's suggestion, record a macro, you should then be able to adjust the code.. (or post on here!)

 

[JackDanIce]

Which I now just did:

With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    .Sort key1:=Range("A1"), order1:=xlAscending, header:=xlGuess
    .AdvancedFilter xlFilterCopy, copytorange:=Range("C1"), Unique:=xlYes
End With

 

[RichardMGreen]

Nice one, thanks.
I'll have a play around and see what I can come up with.

 

[RichardMGreen]

OK, this is my code for creating the unique list:-

    Range("G" & Range("G3").End(xlDown).Row& & ":G" & Range("G65535").End(xlUp).Row).Copy
    Range("T1").PasteSpecial xlPasteValues
    Range("Q" & Range("Q3").End(xlDown).Row& & ":Q" & Range("Q65535").End(xlUp).Row).Copy
    Range("T65535").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Range("T1:T65535").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("W2" _
        ), Unique:=True

It could probably do with some tidying but it works.

The only oddity I've found it that it defines a named range in W2 called "Extract" and whatever value is in there is duplicated in W3.

Anyone any ideas as to why?

 

[JackDanIce]

Assumes W2 is the header and so keeps it, I've not yet found a way to make advanced filter work and not do this

 

[RichardMGreen]

Solved it. Copied a header across as part of the code and let it use that. Don't know if it stops the named range being created but it does stop the duplication of the first value.