merging two files, sample attached « Chandoo.org - Learn Excel & Charting Online

merging two files, sample attached

(3 posts) (2 voices)

Tags:

No tags yet.

dgavilanes

Member
Joined: Jul '12
Posts: 56

link:http://sdrv.ms/PUP7rT

Is there a way to merge these two workbooks into one so the end product will look like this. (21 rows per each "un" with a total of 11,151 rows)

Un yr num
2130 2010 3
2130 2010 4
2130 2010 5
2130 2010 6
2130 2010 7
2130 2010 8
2130 2010 38
2130 2011 3
2130 2011 4
2130 2011 5
2130 2011 6
2130 2011 7
2130 2011 8
2130 2011 38
2130 2012 3
2130 2012 4
2130 2012 5
2130 2012 6
2130 2012 7
2130 2012 8
2130 2012 38
Any suggestions will be greatly appreciated

Dennis

Posted 8 months ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,080

Hi Dennis ,

Use the following formulae :

In cell A2 : =OFFSET(Data_Range,INT((ROW($B2)-ROW($B$2))/ROWS(comb)),0)

In cell B2 : =OFFSET(comb,MOD(ROW(A2)-ROW($A$2),ROWS(comb)),0)

In cell C2 : =OFFSET(comb,MOD(ROW(A2)-ROW($A$2),ROWS(comb)),1)

All the above formulae are array formulae , entered using CTRL SHIFT ENTER.

Data_Range is : =un!$A$2:$A$532

comb is : =comb!$B$2:$C$22

Narayan

Posted 8 months ago #
dgavilanes

Member
Joined: Jul '12
Posts: 56

Thank you!!!!,I will give it a try

Posted 8 months ago #

You must log in to post.

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

Learn Excel - Topics

Our Products

Full Training Programs
» Excel School
» Excel Dashboard Classes
» Excel VBA Classes
» Excel Financial Modeling
» Excel for PMs
Crash Courses
» Excel Formula Course
» Excel Dashboards Course
Downloads
» Excel PM Templates
» Excel Formula E-book

About us

Username	Password
Remember me