Getting all possible combinations of two tables/columns

[flero94]

Hi,
I have a probably very easy problem but I can't find a solution probably because I don't know the right excel vocabularies.

I have two columns or tables (doesn't matter), one with names and one other with actions like:

names
--------
James
Nathaly
May
...

actions
---------
cleaning windows
cleaning floor
washing
...

now I wont to get a new table where I have all possible combinations of the entries from the both different tables/columns like:

names ... | actions
-----------------------------------------
James .... | cleaning windows
James .... | cleaning floor
James .... | washing
Nathaly .. | cleaning windows
Nathaly .. | cleaning floor
Nathaly .. | washing
May ....... | cleaning windows
May ....... | cleaning floor
May ....... | washing
...

I was googling for adjacency lists and all possible combinations between two lists but can't find anything relating to my issue

If anyone has a solution I would really appreciate that.

[avk]

If name in "A1:A3" & action in "B1:B3"
Enter formula in "F1"

Please Login or Register  to view this content.

Copy Paste F1 formula down until empty cell.

[flero94]

Thanks for the fast answer! I thought I exactly did how you mentioned but I always get the error message "there is a problem with this formula".

-I have my names in A1:A3
-I have my actions in B1:B3
-I copied the code in F1
-And I copied that few lines down.

Screenshot 2017-04-26 14.17.08.png

[samba_ravi]

Please attach a sample excel file

[flero94]

Here is the link to the example file! Thank you a lot for your help
https://www.dropbox.com/s/xh5llp07mw...mple.xlsx?dl=0

[FlameRetired]

Another way.

Edited first formula below. Should be COUNTA($B$2:$B$20)

Row\Col	A	B	C	D	E
1	names	actions		names	actions
2	James	cleaning windows		James	cleaning windows
3	Nathaly	cleaning floor		James	cleaning floor
4	May	washing		James	washing
5				Nathaly	cleaning windows
6				Nathaly	cleaning floor
7				Nathaly	washing
8				May	cleaning windows
9				May	cleaning floor
10				May	washing
11
12

In D2 this formula filled down until you get blanks.

Please Login or Register  to view this content.

and in E2

Please Login or Register  to view this content.

[flero94]

Thanks for the help now it is working for me.

Because I have a german Office version it was necessary to translate all commands (even the if command ). Also I had to switch the "," to ";" maybe it's also a language thing or it is just a configuration thing. Probably that was the issue with the first answer as well. Below I post the german code maybe it helps anyone:

For D2

Please Login or Register  to view this content.

For E2

Please Login or Register  to view this content.

Thanks again, you are were a greate and fast help.

[FlameRetired]

flero94 my apologies.

I failed to note German location. If I would have uploaded an Excel workbook with the solution Excel would have handled the regional settings. I am not sure of the translation, though.

My bad.

Glad to hear you got it to work. Thank you for the feedback.

[flero94]

No worries, it was not your fail. The actual problem is that Microsoft started to translate everything. It would be so easy just to use the English words. Everyone understand them and all other programs / programming languages are using them. In the last time I even learned new german words because microsoft was translating them, but everyone was using the english ones before.

[Pete_UK]

Here's another way (with file attached so that you can see the translated formulae). I put 4 names in A2:A5 and the three actions in B2:B4, then this formula in D2:

=IF(ROWS($1:1)>COUNTA(A:A)*COUNTA(B:B),"",INDEX($A$2:$A$5,INT((ROWS($1:1)-1)/COUNTA(B:B))+1))

and this formula in E2:

=IF(D2="","",INDEX($B$2:$B$4,MOD(ROWS($1:1)-1,COUNTA(B:B))+1))

The formulae were then copied down to row 25, i.e. far more than is required. If you have another name, change the $A$2:$A$5 in the first formula.

Hope this helps.

Pete

P.S. When you type a function name in Excel as part of a formula and then press Enter, Excel has to try to make sense of the string that you have typed in. In parsing that string, Excel will look for the function names and translate them into a token (some number), which then makes it easier to translate the function names into words for different languages. So, if I have a formula with INDEX in it, that will be stored as a token, then if the workbook is opened in a French language version, the equivalent name for INDEX will appear for that French user.

The use of commas vs semicolons is governed by the List Separator as defined in a user's Regional Settings. Generally, in Continental Europe a comma is used instead of a full-stop to denote a fractional decimal number, e.g. 3,142 to denote PI. Consequently, a comma can't be used as a list separator as decimal values would confuse the parser, and so a semicolon is used instead.