Help with Merge in Excel

[Keimal]

Hi all,

I have recently had to create a bunch of excel macros, but there is one that I cannot figure out how to do. I currently have two spreadsheets (there are many from the same workbook) that I want to merge. However, both spreadsheets don't have the same number of columns, and they may contain duplicates.

I've attached an example that includes the two spreadsheets I need to merge (Gate Validation and Gate Names) and the desired output (Updated Gate Validation).

I am not that familiar with VBA so I wanted to see if any of you guys had a good idea of how to do this.

Any help is greatly appreciated!

-Keimal

[Keimal]

I still have this issue if anyone is willing to help.

[Paul_Hossler]

Try this



Option Explicit

Sub GateUpdate()
Dim rGates As Range, rValid As Range
Dim iGate As Long, iValid As Long
Set rGates = Worksheets("Gate Names").Cells(1, 1).CurrentRegion
Set rValid = Worksheets("Gate Validation").Cells(1, 1).CurrentRegion
For iValid = 2 To rValid.Rows.Count
iGate = 0
On Error Resume Next
iGate = Application.WorksheetFunction.Match(rValid.Cells(iValid, 1).Value, rGates.Columns(1), 0)
On Error GoTo 0
If iGate > 0 Then
rValid.Cells(iValid, 2).Value = rGates.Cells(iGate, 2).Value
rValid.Cells(iValid, 3).Value = rGates.Cells(iGate, 3).Value
End If
Next iValid
For iGate = 2 To rGates.Rows.Count
iValid = 0
On Error Resume Next
iValid = Application.WorksheetFunction.Match(rGates.Cells(iGate, 1).Value, rValid.Columns(1), 0)
On Error GoTo 0
If iValid = 0 Then
rValid.Cells(1, 1).End(xlDown).Offset(1, 0).Value = rGates.Cells(iGate, 1).Value
rValid.Cells(1, 1).End(xlDown).Offset(0, 1).Value = rGates.Cells(iGate, 2).Value
rValid.Cells(1, 1).End(xlDown).Offset(0, 2).Value = rGates.Cells(iGate, 3).Value
End If
Next iGate
End Sub