Merge Worksheets in Excel 2010 3

[Sweetworks]

I have two worksheets I want to Combine.

Sheet 1 has two columns, and 141 Rows
Row 1 is a Header Row, so there are 140 Lines of Data.

Sheet 2 has one column, and 255 Rows.
Row 1 is a Header Row, so there are 254 Lines of Data.

Examples
Sheet 1
Business_Unit Line_Of_Business
InterCompany 90001
InterCompany 90002


Sheet 2
Country_Code
AC
AD
AE

I want to combine them to look like this
Business_Unit Line_Of_Business Country_Code
InterCompany 90001 AC
InterCompany 90001 AD
InterCompany 90001 AE
InterCompany 90002 AC
InterCompany 90002 AD
InterCompany 90002 AE



It basically will take the First row of Data and repeat it as many times are there are rows of data in the second sheet, and then duplicate with the second row.

Is there any function in Excel that will automate this?
It is basically 35560 Lines and before I start creating it, I wanted to make sure it is not some simple function.

 

[SkipVought]

Hi,

That sounds to me like a cartesian join.

You could do that using MS Query in about 2 minutes

Select a.*, b.*
From [Sheet1$] a, [Sheet2$] b

On a new sheet...

Data > Get External Data > From other sources > From Microsoft Query > Excel Files* -- and drill down to YOUR WORKBOOK. Make sure that OPTIONS shows ALL BOXES CHECKED, and you will see your sheets as table sources to code as I have posted above.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Would VBA solution be OK?

Have fun.

---- Andy

 

[Sweetworks]

Awesome.. It's alot easier to know how to do something when you know what it is called.

I followed your steps but i did not see the System Tables.

Googled it and found this..

http://www.contextures.com/excelmsquerycartesian.html

Took 10 seconds, what would have take me a hour or so..

Thanks.

 

[lionelhill]

Stars all round, Skip for reminding me of MS Query, which I always forget, and Sweetworks for the clearest description of the problem, and a really useful link. Thanks, both!