You are here:

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

Advertisement


Question
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

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

Sub combine()
   Sheet3.UsedRange.ClearContents
   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
       Next
       k = j
   Next
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

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 http://www.thumbtack.com/ny/new-york/excel-training/

Experience

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."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

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

©2016 About.com. All rights reserved.