How to transpose one tab of data into another tab?

Question

I'm looking for guidance on how to set up my Apps Script file to transpose data from one tab to another. A sample file of my data can be found here.

I currently have 10 columns in the rawData tab: [date], [submitter], [program a - region], [program a - first choice], [program a - second choice], [program a - third choice], and then repeat col 3, 4, 5, 6 for program b.

I would like to transpose each value that shows up in any of the first/second/third choice columns into a separate row that contains the date, submitter, region, and program a/b (this value would be assigned based on where values show up).

What I would like to achieve can be found here.

I've found resources that allow me to transpose the full table into a row, but I do not know how to avoid NULL values or assign values for a column.

Any tips or guidance is appreciated.

Answer 1

No need for a script. This answer uses a series of stacked queries.

---

=query({ IFERROR(Query({A1:A,B1:J,arrayformula("a"&K1:K)},"Select Col1, Col2, Col11, Col3, Col4 where Col1 is not null and Col3 is not null and Col4 is not null label Col3 'region',Col4 'user', Col11 'program'",1),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("a"&K1:K)},"Select Col1, Col2, Col11, Col3, Col5 where Col1 is not null and Col3 is not null and Col5 is not null ",0),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("a"&K1:K)},"Select Col1, Col2, Col11, Col3, Col6 where Col1 is not null and Col3 is not null and Col6 is not null ",0),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("b"&K1:K)},"Select Col1, Col2, Col11, Col7, Col8 where Col1 is not null and Col7 is not null and Col8 is not null ",0),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("b"&K1:K)},"Select Col1, Col2, Col11, Col7, Col9 where Col1 is not null and Col7 is not null and Col9 is not null ",0),{"","","","",""}); IFERROR(Query({A1:A,B1:J,arrayformula("b"&K1:K)},"Select Col1, Col2, Col11, Col7, Col10 where Col1 is not null and Col7 is not null and Col10 is not null"),{"","","","",""})} ,"select Col1,Col2,Col3,Col4,Col5 where Col1 is not null order by Col1 format Col1 'd/M/yyyy'")

---

SAMPLE OUTPUT

There are six stacked queries

• #1: Program A - First choice
• #2: Program A - Second choice
• #3: Program A - Third choice
• #4: Program B - First choice
• #5: Program B - Second choice
• #6: Program B - Third choice

These have a consistent format:
IFERROR(Query({A1:A,B1:J,arrayformula("a"&K1:K)},"Select Col1, Col2, Col11, Col3, Col5 where Col1 is not null and Col3 is not null and Col5 is not null ",0),{"","","","",""})

Points to note:

• IFERROR is required in case the query returns an empty output. Normally an empty output returns #N/A, but the IFERROR enables us to return an empty array: {"","","","",""}.

• arrayformula("a"&K1:K): the desired output returns a lower case value for the "Program". This is the method used to create program name. Column K is used as a help column.

• The stacked queries are wrapped in a final query with this clause: "select Col1,Col2,Col3,Col4,Col5 where Col1 is not null order by Col1 format Col1 'd/M/yyyy'"

  • select Col1,Col2,Col3,Col4,Col5: return the columns of data
  • where Col1 is not null: this drops any of the empty output queries
  • order by Col1: Order by date
  • format Col1 'd/M/yyyy': format the date
    • When the query() function is wrapped in the iferror() function, the format clause in the query() function will only work if the column is formatted as Automatic. But the iferror() overrides the column Automatic format and returns a number value. So, the final query (which is NOT wrapped in IFERROR() includes a format clause for the data values. HT: Ben Liebrand in Sheets query function loses date format when inside 'IFERROR()'

Answer 2

I will throw my hat in the ring on this one.

Delete everything from your "formattedData" sheet, including headers. Then place the following formula in cell A1:

=ArrayFormula({SPLIT("Date~Submitted By~Program~Region~User","~"); QUERY(SPLIT(FLATTEN(FILTER(rawData!A2:A & "~" & rawData!B2:B & "~" & {TRIM(REGEXEXTRACT(rawData!C1, "[^-]+")) & "~" & rawData!C2:C & "~" & rawData!D2:F, TRIM(REGEXEXTRACT(rawData!G1, "[^-]+")) & "~" & rawData!G2:G & "~" & rawData!H2:J}, rawData!A2:A<>"")), "~", 1, 0), "Select * WHERE Col5 Is Not Null")})

This one formula will produce all headers and results.

Spacing between components within the formula is for ease of reading only and can be removed if desired.

The formula assumes that "program a" and "program b" are just dummy/placeholder names, and that actual program names will be used in your real-world spreadsheet. However, the use of a dash after the program name in the "rawData" sheet headers is important, since the formula is written to use that to extract the program names. If you change this, be sure to change the dash in the two REGEX clauses to whatever the new character is.

The one note I will add is that all of the instances of & "~" & are just concatenating groupings of data with a delineator of choice (I chose the tilde, since it is unlikely to turn up in actual data); and later, the SPLIT will separate those data groupings at this delineator.

Typically, I do not share complex formulas on this forum. This time, I have. However, beyond the note above, I will not have time to explain in detail. I encourage any interested parties to take the formula apart and put it back together again for understanding, if that is of interest.