I have two cells in google sheets that look like this:
I want to concatenate both of them given a certain condition, that the first element of the first cell concatenates with all the elements of the second cell until it gets to the line break. Then, the second element of the first cell should do the same after the line break. After the elements are concatenated, I want to generate a list of each element:
The resoult should look like this:
Can you please help me find a solutions using google sheets or apps script?
Thanks!
=LET(tables,TRANSPOSE(INDEX(SPLIT(A1,CHAR(10)&CHAR(10),0,1))),
namings,TRANSPOSE(INDEX(SPLIT(B1,CHAR(10)&CHAR(10),0,1))),
TOCOL(REDUCE(,SEQUENCE(COUNTA(namings)),LAMBDA(a,v,{a;INDEX(INDEX(tables,v)&TRANSPOSE(SPLIT(INDEX(namings,v),CHAR(10),0,1)))})),1))
Here you have an option. In the first two lines it splits them by the two line breaks CHAR(10)
, and then with REDUCE it stacks the different combinations of elements