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. keyvaluekey1a,b,ckey2a,bkey3b,c,d (could have mode than 3 values) and want to get the output : key1a,bkey1b,ckey1a,ckey2a,bkey3b,ckey3c,dkey3b,d Is there an easy way to do this ? Thank you

list to combination of pairs

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

Parse

Transformation

Join

Preparation

Expression

Accepted answers

jdunkerley79

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)

SelfJon.yxmd

All comments

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

SelfJon.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703