-1

I've a spreadsheet with values in several columns like this scheme

value1  value2  value3

And I'd like to couple every value with each other like this scheme:

value1  value2
value1  value3
value2  value3

Is there any excel funtion or python code or OpenRefine function that does this task?

Community
  • 1
  • 1
Andrea Angeli
  • 131
  • 1
  • 16

1 Answers1

1

In OpenRefine you can do this in three steps. First get all the values into a single cell with a separator between each value that isn't used in any of the text:

From the dropdown menu in one of the Columns choose menu "Edit Column->Add column based on this column" Use the GREL:

forEach(row.columnNames,cn,cells[cn].value).join("|")

You should have a new column containing the values all joined together in a single cell:

value1|value2|value3

Now create the combinations of the terms - from the dropdown menu on your new column choose menu "Edit Cells->Transform..." Use the GREL:

forEachIndex(value.split("|"),i,v,forRange(i+1,value.split("|").length(),1,j,v.trim() + "," + value.split("|")[j].trim()).join("|")).join("|")

This should give you a list of the combinations with the values separated by commas and the combinations separated by the pipe character (feel free to substitute other join characters in the GREL above for different separators):

value1,value2|value1,value3|value2,value3|

Then finally on the same column use the menu item "Edit cells->Split multi-valued cells..." specifying the pipe '|' character as the separator. You'll now have a column which contains each of the combinations:

value1,value2
value1,value3
value2,value3
Owen Stephens
  • 1,550
  • 1
  • 8
  • 10