1

Could someone help me to solve the task?

I have a Google Form, that collect information about meetings (date, tasks, deadline). I need to combine date from every row in one cell with the following logic.
Date of the meeting (maximum date from 10 cells)
Task 1 of this meeting (deadline) -
Task 2 of this meeting (deadline)

Date of the meeting (second maximum date from 10 cells)
Task 1 of this meeting (deadline) -
Task 2 of this meeting (deadline)

Date of the meeting (last (10) maximum date from 10 cells)
Task 1 of this meeting (deadline) -
Task 2 of this meeting (deadline)

Number of meetings in each row can be from 0 to 10
Number of tasks from 1 to 5

It would be great to solve task with arrayrange formula.

Also link with an example https://docs.google.com/spreadsheets/d/1eGo5M1P1OmPU09jND7clc2fn7Z3Awe2JEpf2IhNvuE0/edit?usp=sharing

0
=ArrayFormula(if(CD2:CD="";; vlookup(row(CD2:CD)&""; trim(split(transpose(query(query(split(transpose(split(textjoin(""; true; if(CD2:CD="";; if((mod(column(CD2:GS)-9; 12) = 1) * (CD2:GS <> ""); "\" & row(CD2:GS) & "~|" & CD2:GS & "|" & char(10) & "Meeting from " & text(CD2:GS; "dd.mm.yyyy"); if(isodd(column(CD2:GS)-9) * (CD2:GS<>""); char(10) & (mod(column(CD2:GS)-9;12) - 1)/2 & ". " & CD2:GS & " (" & text(CE2:GT; "dd.mm.yyyy") & ")"; ))));"\")); "|"); "select Col2, max(Col3) group by Col2 pivot Col1 order by Col2 desc label Col2 'date' ");; 999)); "~"));2;false)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.