tpoynton
08-07-2007, 01:18 PM
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:
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
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
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:
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
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