Combine Text Lists

[Raysoc]

I was looking thru the Mr Excel book and ive been googling this, have yet to find a suitable solution.

I have a list of product codes generated weekly, some weeks not all the product codes show, and some new ones might get added. So in a week over week or month over month view I need all the entries.

So I want a combined list of all the entries in Column A minus duplicates. I know how to do this the long way... copy each new week into the original list then do an advanced filter removing dups, but it would be nice if there was an easier way to just compare two lists and add the missing entries to the original..

I tried using data consolidate but doesn't seem to work..

Any ideas?

 

[Georgiboy]

Something like this will prevent you having duplicates in the fist place...

Sub Compare()
Dim rCell As Range
Dim FromRange As Range, ToRange As Range
Dim EndRowFrom As Integer, EndRowTo As Integer

' end of data in the from sheet
EndRowFrom = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
' end of data in the to sheet
EndRowTo = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

' range to look in (weekly sheet for example)(from sheet)
Set FromRange = Sheets("Sheet2").Range("A1:A" & EndRowFrom)
' range of data used for counting duplicates (original list)(to sheet)
Set ToRange = Sheets("Sheet1").Range("A1:A" & EndRowTo)

' a loop to see if the data in the from sheet is present in the to sheet
' if it is not then copy it to the original list under the last entry
For Each rCell In FromRange.Cells
    If WorksheetFunction.CountIf(ToRange, rCell.Value) < 1 Then
        rCell.Copy Destination:=Sheets("Sheet1").Range("A" & EndRowTo + 1)
        EndRowTo = EndRowTo + 1
    End If
Next

End Sub

Hope this helps