Combin Two lists to one

[nd0911]

Hello,

I have Customers list that start in cell B4 that looks like this:

Bob
John
Andy
Robert

And I have a Products list that start in cell D3 and looks like this:
Sony PS
XBOX


My want to create a forumlas (i gess an array formulas) that create this table:

Product | Customer
Sony PS | Bob
Sony PS | John
Sony PS | Andy
Sony PS | Robert
XBOX | Bob
XBOX | John
XBOX | Andy
XBOX | Robert


By the way, how can I attache a excel file for example ?

Thank you !

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​	G​
1​		List1​		List2​		Product​	Customer​
2​		Bob​		Sony PS​		Sony PS​	Bob​
3​		John​		XBOX​		Sony PS​	John​
4​		Andy​				Sony PS​	Andy​
5​		Robert​				Sony PS​	Robert​
6​						XBOX​	Bob​
7​						XBOX​	John​
8​						XBOX​	Andy​
9​						XBOX​	Robert​
10​

Formula in F2 copied down
=IF(ROWS(F$2:F2)>ROWS(B$2:B$5)*ROWS(D$2:D$3),"",INDEX(D$2:D$3,INT((ROWS(F$2:F2)-1)/ROWS(B$2:B$5))+1))

Formula in G2 copied down
=IF(F2="","",INDEX(B$2:B$5,MOD(ROWS(G$2:G2)-1,ROWS(B$2:B$5))+1))

Hope this helps

M.

 

[nd0911]

Thank you very much BUT....

I need it to be dynamic (I should have noted that before sorry), for example if I will add one more customer or one more Product...

 

[Marcelo Branco]

Thank you very much BUT....

I need it to be dynamic (I should have noted that before sorry), for example if I will add one more customer or one more Product...

Create dynamic named ranges List1 and List2 (assumes data in Sheet1)

Formulas > Names manager > New
Name: List1
Refers to:=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(REPT("z",255),Sheet1!$B:$B))

Formulas > Names manager > New
Name: List2
Refers to:=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH(REPT("z",255),Sheet1!$D:$D))

and try these formulas
F2 copied down
=IF(ROWS(F$2:F2)>ROWS(List1)*ROWS(List2),"",INDEX(List2,INT((ROWS(F$2:F2)-1)/ROWS(List1))+1))

G2 copied down
=IF(F2="","",INDEX(List1,MOD(ROWS(G$2:G2)-1,ROWS(List1))+1))

M.

 

[sandy666]

or maybe try PowerQuery - easier, faster and dynamic

 

[nd0911]

Perfect !

Tnx.