0

Can anyone help me write a Google Sheets function for doing this:

Google Sheets example of what needs to be done:

Google Sheets example of what needs to be done

The first column could be filled with X lines with a name in each one.

From that names, the columns "target" and "source" would be filled with all possible combinations between the names, without repeating a pair (if you already have "Gustavo | Jacinto", you don't need "Jacinto | Gustavo").

I know I can solve this problem using python and other stuff, but I wanted to do this using Google Sheets' functions because it would be faster to get the response I need.

player0
  • 124,011
  • 12
  • 67
  • 124
Gustavo Luigi
  • 33
  • 4
  • I worked out another way of doing it here https://stackoverflow.com/questions/47922267/how-to-perform-iteration-over-excel-google-sheets-cells-to-get-pairwise-combinat/47932221#47932221 – Tom Sharpe Feb 20 '19 at 10:43

2 Answers2

1
=QUERY(ARRAYFORMULA(SPLIT(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A2:A&CHAR(9)), COUNTA(A2:A)),  CHAR(9)))& " "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(A2:A&CHAR(9),  COUNTA(A2:A))), CHAR(9))), " ")),
 "where Col1<>Col2 order by Col1", 0)

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thanks! It worked really good. **But is there any way to prevent the function from making repetitive relationships like "name1 | name2" and "name2 | name1"?** This would really help. – Gustavo Luigi Feb 19 '19 at 18:11
0
=ARRAYFORMULA(SPLIT(SORT(
 TRANSPOSE(SPLIT(CONCATENATE(REPT(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))&","&
 TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))), (
 UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))<=
 TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))))*
 REGEXMATCH(CONCATENATE(","&SUBSTITUTE(TEXTJOIN(",",1,A2:A),",",",,")&","&CHAR(9)),"(,"&
 UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))&",[^\t]*,"&
 TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),","))))&",)|(,"&
 TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),","))))&",[^\t]*,"&
 UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))&",)"))&CHAR(9)),CHAR(9)))),","))

0

player0
  • 124,011
  • 12
  • 67
  • 124