Merge multiple spreadsheets with common field

**sbarry50** · 10-19-2010, 01:24 AM

Hi I have two spreadsheets with a common field and I need to merge them into a separate third spreadsheet with matching data from both.

I attached an example workbook with simplified sample data.

Sheet 1 has Product_id and SKU fields. Sheet 2 has SKU and Price fields. I need to merge them into Sheet 3 so all three fields are displayed.

Unfortunately in the real data there are literally 8,000+ entries. Roughly 90% of the SKU's are in both spreadsheets, but some SKU's are in one but not the other and visa versa, so sorting, copying and pasting will not work.

I suspect I need the equivalent of IF SKU (Sheet1) = SKU (Sheet2), then print Product_id, SKU and List Price in Sheet 3, but I don't know how to do that in Excel.

Thank you very much for any help you can provide.

**JBeaucaire** · 10-19-2010, 03:12 AM

Like so:

Option Explicit

Sub MergeTwoSheets()
Dim LR As Long

With Sheets("Sheet3")
    .Cells.Clear
    Sheets("Sheet1").Columns("A:A").Copy .Range("B1")
    Sheets("Sheet2").Range("A2:A" & Rows.Count).SpecialCells(xlConstants) _
        .Copy .Range("B" & .Rows.Count).End(xlUp).Offset(1)
    .Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=.Range("A1"), Unique:=True
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A1:A" & LR).Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        sortmethod:=xlPinYin, dataoption1:=xlSortTextAsNumbers
    .Range("B:B").ClearContents
    .Range("B1:C1").Value = [{"SKU","List Price"}]
    .Range("B2:B" & LR).FormulaR1C1 = _
        "=IF(ISNUMBER(MATCH(RC1, Sheet1!C1, 0)), INDEX(Sheet1!C2, MATCH(RC1, Sheet1!C1, 0)), """")"
    .Range("C2:C" & LR).FormulaR1C1 = _
        "=IF(ISNUMBER(MATCH(RC1, Sheet2!C1, 0)), INDEX(Sheet2!C2, MATCH(RC1, Sheet2!C1, 0)), """")"
    .Columns("B:C").Value = Columns("B:C").Value
End With

End Sub

**snb** · 10-19-2010, 04:31 AM

or

Sub snb()
  On Error Resume Next
    
  sn = Sheets(2).Cells(1, 1).CurrentRegion
  sq = Sheets(1).Cells(1, 1).CurrentRegion.Resize(, 3)
  For j = 2 To UBound(sn)
    sq(Sheets(1).Columns(2).Find(sn(j, 1), , xlValues, xlWhole).Row, 3) = sn(j, 2)
  Next

  Sheets(3).Cells(1, 1).Resize(ubound(sq), 3) = sq
End Sub

**sbarry50** · 10-19-2010, 11:42 AM

Thank you for the help! I'll try both of these with the real data. Hopefully I won't run into any issues, but I may have a follow-up question or two if I run into trouble. Thanks again!