list to combination of pairs

[viveknarayananp]

hi,
I am trying to split a list of values into pairs and have their "key" repeat.

I have a list of values, for eg.

key	value
key1	a,b,c
key2	a,b
key3	b,c,d

 (could have mode than  3 values)

and want to get the output :

key1	a,b
key1	b,c
key1	a,c
key2	a,b
key3	b,c
key3	c,d
key3	b,d

Is there an easy way to do this ?

Thank you

[jdunkerley79]

Sure there is probably an easier way but this is do able via a text to columns to break it into rows on comma.

Then add an index column using MultiRow formula.

Create a next value and use this to join onto itself.

 

Attached as a workflow

[michael_treadwell]

I would consider using the R Tool to accomplish this.

There is a function in the utils package called combn that, given a vector (in this case the list of letters in [value]) and an integer (looks like you want 2 in this case), will generate all combinations of [value] taken 2 at a time.

[viveknarayananp]

jdunkerley79
With this it doesnt show all the possible combinations.
key1 b a
key2 b a
key3 c b
key1 c b
key3 d c

is the output and it missed out
key1 c a
key3 d b

but thanks for the idea.. a minor change might solve it.

[jdunkerley79 · Accepted solution]

Try this tweaked version.

Joins on self but only on key

Uses index to exclude self join]

Selects case where values in alphabetical order (i.e. a,b no b,a)

[viveknarayananp]

Thank you jdunkerley79
worked perfectly !