How to generate a list based on two lists of values in Excel 2

[JRUKUSA]

Seems like a simple need, tried searching for a solution!

Have list of names in two columns, column 1 and column 2. Need to generate 2 new columns that assigns all names in column 2 to each name in column 1.

Column 1 = A, B, C
Column 2 = X, Y

Creates the following

Column 3 = A,A,B,B,C,C
Column 4 = X,Y.X,Y,X,Y

Have anything from 20 thru 100 names in column 1 and 100's thru 1000's of names in column 2, so not easy to do manually.

Appreciate any advise

Cheers

 

[SkipVought]

JRUKUSA,

Why transpose values from column1 and column2, when you could simply COPY that data and PASTE IT here.

The solution is to put the data in one column WITH A HEADING, in a separate sheet and do the same to the data in the other column. The result is two separate tables in two separate sheets.

Then on a third sheet, use Data > Get external data > From other sources > Microsoft query...and drill down to your workbook.

Then, using Microsoft Query, perform a Cartesian Join, which will result in the fully joined result you intend.

Select *
From [i][your first sheet], [your second sheet][/i]

These sheet names will be determined by the code that you'll generate in the QBE grid.

 

[SkipVought]

My results from query done in less than 1 minute.
[pre]
Name Name2
A X
A Y
B X
B Y
C X
C Y
[/pre]

 

[SkipVought]

BTW, I referenced how to use MS Query in several threads you posted over the past 8 years.

 

[JRUKUSA]

So sorry, suffering from alzheimer's.

 

[SkipVought]

So sincerely sorry. I have lost two close family members, my father and my wife, to this horrible disease.

 

[Deniall]

Skip has nailed it, as usual.

However if you really MUST achieve your result using basic Excel functions you can do it as follows.[ ] If your A,B,C column starts in cell B10 and your X,Y column starts in cell C10 then put the formula

=INDEX($B$10:$B$12, MOD(ROW()-ROW($B$10),COUNTA($B$10:$B$12))+1 )

in D10, and the formula

=INDEX($C$10:$C$11, INT((ROW()-ROW($C$10))/COUNTA($B$10:$B$12)+1) )

in E10.

Then copy these two formulae down the sheet as far as necessary.

You could probably make things a bit more flexible by using dynamic ranges.