Thread: Merge Multiple accounts to master list

Hi Guys,

Looking to merge multiple account numbers from multiple tabs onto the master list. I have attached a dummy sheet for you to see what I mean.

Basically I need to get rid of all the accounts that come up more then once on all the sheets and just have it once on the master list. Any help would be greatly appreciated

Thanks again
Mikey

Hi mikeydaman

Try this Dictionary....

Option Explicit
Sub ptest()
    Dim n As Long, i As Long, xCell, ws As Worksheet, dic1 As Object
    
   Set dic1 = CreateObject("Scripting.Dictionary")
    dic1.CompareMode = 1
    For Each ws In Worksheets
        If Not ws.Name Like "Master list" Then
            With Worksheets(ws.Name)
                For Each xCell In .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
                    If Not dic1.exists(xCell.Value) Then
                        n = n + 1
                        dic1.Item(xCell.Value) = n
                    End If
                Next
            End With
        End If
    Next
    ThisWorkbook.Sheets("Master list").Cells(1).Resize(n, 1).Value = Application.Transpose(dic1.keys)

End Sub

Hey Pike;

I know we're talking about saving a few miliseconds at best, but would it be faster to consolidate all of the information from each of the different months and then apply:

    ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo

Rather than doing a cell by cell comparison?

Although, that aside... impressive vba method. I'd have just said to manually copy all of the column As and then do the remove duplicates function under data.

Hi Miraun

Yes, but i dont know which version of excel mikeydaman has.
"RemoveDuplicates" is for 2007 and better .. but good thinking

Location Location you must live near me ,, oh not with that IP address!

Sub tst()
 With Sheets("Master list")
  For Each sh In Sheets
   If sh.Name <> .Name Then .Cells(Rows.Count, 1).End(xlUp).Offset(IIf(.Cells(1) = "", 0, 1)).Resize(sh.Columns(1).SpecialCells(2).Count) = sh.Columns(1).SpecialCells(2).Value
  Next
  .Columns(1).AdvancedFilter xlFilterCopy, , .Cells(1, 2), True
  .Columns(1).Delete
 End With
End Sub

Hey Guys,

Thanks for everyones input. I think Im going to do it the old fashioned way.
Sorry I forgot to say that I use 2003 at work. I have 2007 at home and would have used the removed duplicates function. Im going to merge everything to the master list like Miraun said then..

IF(COUNTIF($A$1:$A1,A1)>1,"Duplicate","Fine")

Then filter and custom to equal Duplicate and delete them that way. This way I see each step to make sure Im not getting rid of something I shouldn't be

Thanks for the VBA as well. Gives me options for future.

Hi mikeydaman
can you please complete "MS Office Version" in your user profile