1

The title may be confusing/misleading; I'm frankly having trouble trying to say what I need in a concise manner.

I have 2 lists of distinct values in Excel. 

List A:
1
2
3

List B:
C
D
E

I need to create a sheet that shows a one to many relationship where List A is the 'One' and list B is the 'Many'. So the result would be something like :

Ouput:
1 C
1 D
1 E
2 C
2 D
2 E
3 C
3 D
3 E

The results are not concatenated and are in their own cols/rows. Any suggestions?

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
scarr030
  • 55
  • 7
  • Looks like you just want to [list all possible](https://stackoverflow.com/questions/48651400/how-to-list-all-possible-combinations-of-the-values-in-three-columns-in-excel) [combinations](https://stackoverflow.com/questions/46101847/excel-permutation-table-without-vba) – cybernetic.nomad Dec 11 '19 at 18:50
  • imho OP needs the result in separate cells.. also it is a list combination list, not permutation, I think. – p._phidot_ Dec 12 '19 at 02:34

1 Answers1

1

Assuming list 1 is in A1:A3, list to is in B1:B3. Then in D1 put :

=IF(CEILING(ROW()/ROWS($A$1:$A$3),1)>ROWS($A$1:$A$3),"",INDIRECT("A"&CEILING(ROW()/ROWS($A$1:$A$3),1),TRUE))

and in E1 :

=IF(CEILING(ROW()/ROWS($B$1:$B$3),1)>ROWS($B$1:$B$3),"",INDIRECT("B"&IF(MOD(ROW(),ROWS($B$1:$B$3))=0,ROWS($B$1:$B$3),MOD(ROW(),ROWS($B$1:$B$3))),TRUE))

and drag both downwards.

Idea : Use row() to 'guide' how the which cell will indirect() address to. You can test the given mod() and ceiling function separately to 'examine' how the pattern works. [do ask if you didn't get it.] (:

please share if it works/not.

p._phidot_
  • 1,913
  • 1
  • 9
  • 17