Split & Transpose challenges

Question

I am struggling to make my sheet work. I have 5 columns, Column 1 = permit number, Column 2 = individuals names, column 3 = locations and column 4 = duration start date, column 5 = duration end date I would like to transpose on a new sheet where column A would be Permit numbers, column B individuals under each other, column C locations under each other and column D and E start & end range.

Data are coming on a Gform and the same person can be on the same permit. Does it make sence?

For example: Snip enclosed here

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

Data are coming on a Gform and the same person can be on the same permit. Does it make sence?

For example: Snip enclosed here

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

user_5632629 · Answer

try:

=ARRAYFORMULA(SUBSTITUTE(TRIM(SPLIT(QUERY(FLATTEN(IF(
 IFERROR(SPLIT(B2:B, ","))="",,"♦"&TO_TEXT(A2:A)&"♣"&
 SPLIT(B2:B, ",")&"♣"&C2:C&"♣"&TO_TEXT(D2:D)&"♣"&TO_TEXT(E2:E))), 
 "where Col1 is not null", 0), "♣")), "♦", ))

Split & Transpose challenges

1 Answers