You are here:

Excel/merge data from two sheets with one same column and duplicate data in same column


i want merge data from two table with one same column and duplicate data in same column
for example:

sheet1: id-post
column A     column B
1024          operator
1031          worker
1032          operator
1040          management
1048          worker
1061          operator

sheet2: Post-training
column A     column B
operator        course1
operator        course2
operator        course4
operator        course6
worker          course2
worker          course3
management      course1
management      course3
management      course5
management      course6

the result in sheet 3 must be:
column A        column B        column C
1024          operator        course1
1024          operator        course2
1024          operator        course4
1024          operator        course6
1031          worker          course2
1031          worker          course3
1032          operator        course1
1032          operator        course2
1032          operator        course4
1032          operator        course6
1040          management      course1
1040          management      course3
1040          management      course5
1040          management      course6
1048          worker          course2
1048          worker          course3
1061          operator        course1
1061          operator        course2
1061          operator        course4
1061          operator        course6

data in sheet1 and sheet2 maybe changed that sheet3 must be update

excuse me for bad English
tank you

run this code when you want sheet3 to reflect the values in sheet1 & 2:

Sub combine()
   k = 1
   m = 1
   For i = 1 To Sheet1.Range("B50000").End(xlUp).Row
       n = Application.CountIf(Sheet2.Range("A:A"), Sheet1.Cells(i, 2).Value)
       nn = Application.Match(Sheet1.Cells(i, 2).Value, Sheet2.Columns(1), 0)
       For j = 1 To n
         Sheet3.Cells(m, 1).Value = Sheet1.Cells(i, 1).Value
         Sheet3.Cells(m, 2).Value = Sheet1.Cells(i, 2).Value
         Sheet3.Cells(m, 3).Value = Sheet2.Cells(nn + j - 1, 2).Value
         m = m + 1
       k = j
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Bob Umlas


I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at


Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Excellence, The Expert, Microsoft

BA in math, Hofstra University, 1965

Awards and Honors
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 All rights reserved.