-2

I'm working on a Gephi project where I need to define links between people (who worked with who).

I have a Google Sheet document with names in each row. Ex:

  • John | Mary | Brian | Dave
  • Emily | David | Sara

I'm looking for a function that could display me then who worked with who in two columns. This way:

  • John | Mary
  • John | Brian
  • John | Dave
  • Mary | Brian
  • Mary | Dave
  • Brian | Dave
  • Emily | David
  • Emily | Sara
  • David | Sara

If you think Google Sheets ain't the thing and it's possible to do it on Excel, you can send me a suggestion too.

player0
  • 124,011
  • 12
  • 67
  • 124
Ravachol
  • 1
  • 1

1 Answers1

0

If someone is looking for the answer, I found it this morning.

All I had to do was edit the code on this Stack Overflow question Google Sheets - Function to combine cells in a column into two columns with all possible combinations?

Change the cell range containing your data (for example: A1 to F1), and paste the code in a new cell.

Example:

=ARRAYFORMULA(SPLIT(SORT(TRANSPOSE(SPLIT(CONCATENATE(REPT(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A1:F1)),",")))&","& TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",", ,A1:F1)),",")))), ( UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A1:F1)),",")))<= TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A1:F1)),",")))))* REGEXMATCH(CONCATENATE(","&SUBSTITUTE(TEXTJOIN(",",1,A1:F1),",",",,")&","&CHAR(9)),"(,"&  UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A1:F1)),",")))&",[^\t]*,"& TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A1:F1)),","))))&",)|(,"& TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A1:F1)),","))))&",[^\t]*,"& UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",", ,A1:F1)),",")))&",)"))&CHAR(9)),CHAR(9)))),","))
Ravachol
  • 1
  • 1