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
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), "♣")), "♦", ))