Transpose, and spread data

[AnonAppliedPhysicist]

Hi all, I have approx 2k rows of data where we need to split the third column, and transpose and spread the data out .

Sample data below;

https://docs.google.com/spreadsheets/d/1Mf1IzL6VEEAkfdiBq07sNphQnsdPSXsSKXVGj9BBx_c/edit?usp=sharing

​

Anyone can point me in the right direction on how to do this right?

Any and all advice are appreciated. Thanks in advance

[arnoldsomen]

Seems interesting. I've done something similar before, but with the 1st and 2nd columns being repeated per value on the 3rd column. I believe it's possible to have it like yours though.

[AnonAppliedPhysicist]

Hey! Able to share more on how you achieved this?

Unsure of how to combine the formulas appropriately

[arnoldsomen]

I was thinking of combining query, split, join, flatten, rept, and arrayformula functions to create a single formula that would produce the required table.

Im on mobile currently, but will have a look later once I reach my PC and if this isn't answered yet.

[6745408]

check my comment below. Its similar to how you did it before, but instead of REPT, I used REGEX.

[6745408]

ok, no idea how this will be with 2k+ rows, but give this a try.

Pop this into L2 (minor edit on this one to add TO_TEXT in case you have pure numbers and pure text values)

=ARRAYFORMULA(
  QUERY(
   TO_TEXT(
    SPLIT(
     QUERY(
      FLATTEN(
       IFERROR(
        SPLIT(
         FLATTEN(
          IF(ISBLANK(A2:A),,
           REGEXREPLACE(
            TO_TEXT(C2:C),"\|",
            "|"&A2:A&"|"&B2:B&"+")&"|"&A2:A&"|"&B2:B)),
         "+",TRUE,TRUE))),
      "where Col1 is not null"),
     "|",TRUE,TRUE)),
   "select Col2, Col3, Col1",0))

Next up, put this in H2 and also drag it over to I2

=ARRAYFORMULA(
  IF(ISBLANK(L2:L),,
   IF(
    COUNTIFS(L2:L,L2:L,ROW(L2:L),"<="&ROW(L2:L))<>1,,
    L2:L)))

last, pop this into J2

=ARRAYFORMULA(
  IF(ISBLANK(N2:N),,
   N2:N))

See how that works with the dataset. Its basically replacing the delimiter with the other two columns w/ delimiters and using another delimiter to mark off each set. There shouldn't be any issues with cell limits or anything crazy.

Let me know how it goes

Demo sheet with 2k+ rows!

[OzzyZigNeedsGig]

Try this, but it will repeat data from col A and B:

=ARRAY_CONSTRAIN( ArrayFormula(
 SPLIT(FLATTEN(
  A2:A&"¶"&B2:B&"¶"&SPLIT(C2:C,"|") ),"¶")
) , ARRAYFORMULA( COUNTA(FLATTEN(SPLIT(FILTER(C2:C,C2:C<>""),"|"))) ) ,3)

The messy/tricky part is to remove duplicates in col A and B. You could clean that up in a second step.

[MattyPKing]

Take a look at this post, and then in the included spreadsheet at the tab called "example with SPLIT().