combining multiple sheets that have duplicates

[donifar]

I have a workbook that has many worksheets. Each worksheet has addresses on them. There are duplicates, in that sheet A, B, C may have 100 names, but sheet B will have 10 that are on A already and C will have 10 from A and 10 from B. I need a way to combine all three (or more) worksheets into one sheet, with no duplicates (based on the address1 and address2 column). I need to be able to choose which sheet has priority, so if B gets copied before A before C.

Any help would be MUCH appreciated. Thank you in advance.

 

[Damon Ostrander]

Hi donifar,

My suggested approach would be:

1. order the sheet tabs in priority order, then select all the sheets you want combined (using Ctrl-Click on the worksheet tabs).

2. write a macro that first copies the first priority selected sheet to a new worksheet.

3. the macro then loops through the remaining selected sheets looping through all rows and checking to see if any rows have both address1 and address2 columns that are NOT already on the new sheet. If so, append the row to the end of the new sheet. This can be made most efficient by looking just for an address1 match before checking address2, because if no address1 match is found there is no need to look for an address2 match.

I hope this helps.

Damon

 

[donifar]

It certainly does! however, I don't know how to write said macro. Is there an automated way, or do i have to write code?

This site is amazing. i have found out so many things, and the people are so helpful!

Hi donifar,

My suggested approach would be:

1. order the sheet tabs in priority order, then select all the sheets you want combined (using Ctrl-Click on the worksheet tabs).

2. write a macro that first copies the first priority selected sheet to a new worksheet.

3. the macro then loops through the remaining selected sheets looping through all rows and checking to see if any rows have both address1 and address2 columns that are NOT already on the new sheet. If so, append the row to the end of the new sheet. This can be made most efficient by looking just for an address1 match before checking address2, because if no address1 match is found there is no need to look for an address2 match.

I hope this helps.

Damon

 

[Damon Ostrander]

Hi again donifar,

Yes, this is something that requires writing code. If the task could be done manually, then you could record the steps and have the code generated automatically. But this particular problem is not difficult to code from scratch once you have a reasonable working knowledge of VBA and the Excel object model.

If you would like to tell me which columns contain the address1 and address2 data, I could provide some code when I get the time.

Or, if you want to email me the workbook I might even incorporate the code for you.

Damon

 

[donifar]

WOW! that is very nice of you! the columns are F and G. and i would rather read the code and get it into excel myself, this way i learn, rather than having you just smack it in there for me.

once again, thanks much!

Hi again donifar,

Yes, this is something that requires writing code. If the task could be done manually, then you could record the steps and have the code generated automatically. But this particular problem is not difficult to code from scratch once you have a reasonable working knowledge of VBA and the Excel object model.

If you would like to tell me which columns contain the address1 and address2 data, I could provide some code when I get the time.

Or, if you want to email me the workbook I might even incorporate the code for you.

Damon

 

[Damon Ostrander]

Hi donifar,

Okay, here is that code:

Option Explicit

Sub MergeAddresses()

Dim Shts As New Collection 'collection of selected sheets
Dim NewWS As Worksheet 'new merged worksheet
Dim WS As Worksheet
Dim iWS As Integer
Dim iRow As Long 'row number on source worksheet
Dim iRows As Long 'number of rows on source worksheet
Dim nRow As Long 'row number on new worksheet
Dim nRows As Long 'number of rows on new worksheet
Dim mCount As Long 'count of added addresses
Dim nDups As Long 'duplicates skipped

'put selected sheets in collection in tab (priority) order
For Each WS In ActiveWindow.SelectedSheets
Shts.Add WS
Next WS

If Shts.Count = 1 Then
MsgBox "Merging requires more than one sheet selected", _
vbOKOnly, "Merge Addresses"
Exit Sub
End If

'create destination worksheet
Shts(1).Copy before:=Worksheets(1)
Set NewWS = ActiveSheet

mCount = 0
nDups = 0
nRows = NewWS.Cells(65536, 6).End(xlUp).Row

'loop through selected sheet collection
For iWS = 2 To Shts.Count
Set WS = Shts(iWS)
iRows = WS.Cells(65536, 6).End(xlUp).Row

For iRow = 2 To iRows
For nRow = 2 To nRows
'check address1 field
If NewWS.Cells(nRow, 6) = WS.Cells(iRow, 6) Then
If NewWS.Cells(nRow, 7) = WS.Cells(iRow, 7) Then
nDups = nDups + 1
GoTo SkipDup
End If
End If
Next nRow
mCount = mCount + 1
nRows = nRows + 1
WS.Rows(iRow).Copy Destination:=NewWS.Rows(nRow)
SkipDup:
Next iRow

Next iWS

MsgBox mCount & " rows added to " & Shts(1).Name & vbCr & _
nDups & " duplicates skipped.", vbInformation, _
"Merge Address Results"

End Sub


This code should be placed in a standard macro module. To do this go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste this code into the Code pane.

To run this macro on your workbook, first order your worksheet tabs in the priority order for merging the records. You can do this by just dragging the tabs with the mouse. Second, select just the tabs that you want merged. Then run the macro (Alt-TMM, select macro and Run).

Incidentally, I assumed that row 1 on each sheet is a header row, which is why

For iRow = 2 To iRows
For nRow = 2 To nRows

loops both start with row 2. If you have no header row start the loops with 1. If multiple header rows start the loops with the first row that contains addresses.

Let me know if you have any problems.

Damon

 

[donifar]

Damon!
thanks! i am working on another project, but will probably get back to this tomorrow or monday. i will let you know how it goes. once again, THANKS!

Hi donifar,

Okay, here is that code:

...

Let me know if you have any problems.

Damon

 

[donifar]

Damon-
i ran it on a test file, and it was brilliant.

thank you so much for your help!

Hi donifar,

Okay, here is that code:
...
Let me know if you have any problems.

Damon

 

[Damon Ostrander]

donifar,

De nada.



Damon