Solved: merging worksheets based on unique identifier

[tpoynton]

Greetings All,

Been struggling with this one all day...here's the scenario:

trying to build a user interface for merging data from two worksheets in the same workbook, based on a 'unique identifier' that exists in each sheet. I'd like the identifier to be either a number, text, or a number stored as text...although this would likely be much easier if it were just numbers used for the ID.

in the attached sample workbook, I've got a button on a sheet that brings up the userform, two sheets that contain no missing data (nomiss), and two sheets that contain ID numbers that do not exist in the other sheet (miss).

The routine works fine if the ID exists in both sheets, and it appears to work fine (at least with this data) if I select to not include rows in the new merged sheet that do not have an ID number on both sheets. Problem is that I would like to give people the option of merging the sheets together including those rows that do not have the unique ID in both sheets.

The way it basically works is that copies of both sheets are made to not mess up the original data, data are sorted, then the sheet with the longest rowcount is used as the working sheet. it loops through each row of the working sheet from bottom to top, and if a match is found while looping through all cells in the ID column of the other sheet, the data are appended to the first unused column in the working sheet.

The offensive (yes, I mean offensive...i've been working on it that long!) code is here:

[vba]
For i = iLastRow To 2 Step -1
bFoundMatch = False
ID = Cells(i, iUniqueIDcol).Value
For Each cell In otherShtUniqueRange
If ID = cell.Value Then
With Sheets(otherShtName)
.Range(.Cells(cell.Row, 1), .Cells(cell.Row, iColCount)).Copy
End With
Cells(i, iPasteCol).PasteSpecial (xlPasteAll)
bFoundMatch = True
j = j - 1
End If
Next cell

If bFoundMatch = False Then

If CheckBox_Keep = True Then
ActiveSheet.Rows(i).EntireRow.Delete
nRemoved = nRemoved + 1
Else
nAdded = nAdded + 1
With Sheets(otherShtName)
.Range(.Cells(j, 1), .Cells(j, iColCount)).Copy
End With
Rows(i).Insert shift:=xlDown
Cells(i, iPasteCol).PasteSpecial (xlPasteAll)
End If
End If
Application.CutCopyMode = False
Next i
[/vba]

I'm open to any and all suggestions...I suspect that looping through all the ID cells on the other sheet isnt the way to go, but I havent been able to get any other type of loop working...

THANKS, tim

[tpoynton]

SO, I was able to work out a solution with another loop...pretty and elegant it is not (like most of my code), but it does do what it is supposed to...I've attached it here in the off chance someone is looking for something similar. I polished it up a bit more with a progress dialog and some other formatting, but that's not included here - if someone's interested, let me know and I'll pull it out of my main project.

I also learned about conditional compiling, as the Mac apparently doesnt support xlSortTextAsNumbers. Also, userforms with the showmodal property set to false dont work on the Mac, so progress dialogs wont work there either unless there is another way to do it...perhaps loading and unloading a form repeatedly, but that just seems silly...and it would pause code execution...yep, bad idea.

Now I'm hijacking my own thread; I'll start a new one in the mac forum if these things have solutions...