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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

List1​

[/td][td][/td][td]

List2​

[/td][td][/td][td]

Product​

[/td][td]

Customer​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td]

Bob​

[/td][td][/td][td]

Sony PS​

[/td][td][/td][td]

Sony PS​

[/td][td]

Bob​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td]

John​

[/td][td][/td][td]

XBOX​

[/td][td][/td][td]

Sony PS​

[/td][td]

John​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td]

Andy​

[/td][td][/td][td][/td][td][/td][td]

Sony PS​

[/td][td]

Andy​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td]

Robert​

[/td][td][/td][td][/td][td][/td][td]

Sony PS​

[/td][td]

Robert​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

XBOX​

[/td][td]

Bob​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

XBOX​

[/td][td]

John​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

XBOX​

[/td][td]

Andy​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

XBOX​

[/td][td]

Robert​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.