Two Sheets into One - with a hitch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have two Excel spread sheets that I would like to combined into one, however I am facing a slight problem. Both sheets have a column containing an ID Number, and many other columns with various data related only to this ID number. I would like to combine the sheets into one sheet, where rows with the same ID number will have the data from both sheets. My problem is that in each sheet, the ID numbers differ, and each sheet has a few ID numbers the other doesn't, yet they share many common ID numbers. For Example

Sheet #
6001000
6001000
6001000
6001000
6001000
6001000
6001001
6001002
6001002
6001002
6001002
6001002
6001002
6001002
6001003
6001003
6001003

Sheet #
6001000
6001000
6001000
6001000
6001000
6001000
6001000
6001000
6001001
6001001
6001001
6001001
6001002
6001002
6001002
6001002
6001003

So if I was to just copy and paste the data from one sheet to the other, the data would not line up exactly with the correct ID number. I could just line them up one at a time, however, there are 29,182 rows! Does anyone have any ideas on what I could do? I have MSO Excel 2002. Any help would be greatly appreciated!

Thank You
~Dan
 
Thanks for the help Frank, though I don't understand how exactly that will help me; but interesting reading none the less!

Something I didn't think of before, is adding rows/ID numbers where they are lacking on both spread sheets. This will allow me to just copy and paste the columns from one sheet to the other and everything should line up. Is there a way to add rows where numbers are missing? Example

Before - Column #





1

After - Column #

2 - Automatically inserted Row her


5 - Automatically inserted Row her

7 - Automatically inserted Row her

9 - Automatically inserted Row her
1

Is it possible to add rows automatically where numbers are missing like in my example above? Remember that I currently have 29,182 rows, so doing it by hand is out of the question. Any help would be greatly appreciated

Thank You
~Dan
 
Hi Dan,

If you copy the ID #'s from both sheets into one you can use the
following Visual Basic Code to remove duplicates. I forget where I
found this but I think it is what you are looking for. If you are
familiar with Microsoft Access it may be easier to do it in there.

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1
Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Hope it helps.

Ben
 
Back
Top