i able perform cartesian product on multiple columns have single value in every cell. however, wondering if there way transform unflattened data separated delimiter cartesian product
for example
id id2 string string2 123 123;1234;1234 sample; sample; sample other; other i want possible combinations displayed.
here go, split each of 4 columns array on ";" nested loop them all:
sub cartesian() dim mystr1 variant, mystr2 variant, mystr3 variant, mystr4 variant, str1 variant, str2 variant, str3 variant, str4 variant, x long mystr1 = split(range("a2").text, ";") mystr2 = split(range("b2").text, ";") mystr3 = split(range("c2").text, ";") mystr4 = split(range("d2").text, ";") x = 4 each str1 in mystr1 each str2 in mystr2 each str3 in mystr3 each str4 in mystr4 range("a" & x).formula = str1 range("b" & x).formula = str2 range("c" & x).formula = str3 range("d" & x).formula = str4 x = x + 1 next next next next end sub i had data in row 2 , starts posting row 4 onwards. can play around ranges needed.
