transposing paste in right date columns

[massimo vigano]

I'm looking for a way to copy and transposing paste  between Sheets : in sheet 1  I have  the small jobs assigned to some people with a summary line that tells me if they are free (they can do up to 3 jobs M and P)

In sheet "Peter", "John" etc. I have shared planner of that people, where each one marks in his sheet other own commitments divided between morning and afternoon for each day. There are also two spaces M and P where everyone manually marks the existence of commitments with me.

I  would like to copy the data in sheet1 personal row and paste in the M and P columns in the sheet of each of them.

I add an example file

Thank you 

P:S: I’m sorry for my english

[Clark Lind]

This was a fun puzzle! One that Ben Collins would enjoy... 
This is a one-time solution. It will break as soon as the number of days in the month changes... so remember that. 

Formulas for Peter Tab

Cell B4:     =transpose(query({Sheet1!A3:DT3},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Sheet1!C3:BL3);mod(column(Sheet1!C3:BL3)-column(Sheet1!C3),2)}),"Select Col1 where Col2=0"),"Col0")))))

Cell B5:     =transpose(query({Sheet1!A3:DT3},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Sheet1!D3:BL3);mod(column(Sheet1!D3:BL3)-column(Sheet1!D3),2)}),"Select Col1 where Col2=0"),"Col0")))))

Cell B44:   =transpose(query({Sheet1!A3:DT3},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Sheet1!BM3:DT3);mod(column(Sheet1!BM3:DT3)-column(Sheet1!BM3),2)}),"Select Col1 where Col2=0"),"Col0")))))

Cell B45:   =transpose(query({Sheet1!A3:DT3},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Sheet1!BN3:DT3);mod(column(Sheet1!BN3:DT3)-column(Sheet1!BN3),2)}),"Select Col1 where Col2=0"),"Col0")))))

For John Tab and Albert Tab, copy the above formulas to the same cells on their tabs. Then change all references of peter (row 3) (i.e., "A3:DT3", "C3:BL3" and "C3") to Row 4 (John) and row 5 (Albert).

Example, the first one for John Tab:
Cell B4:     =transpose(query({Sheet1!A4:DT4},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Sheet1!C4:BL4);mod(column(Sheet1!C4:BL4)-column(Sheet1!C4),2)}),"Select Col1 where Col2=0"),"Col0")))))

Cell B5: etc etc

[massimo vigano]

I try but I received error. I send the link to limited Google Spreadsheet. 

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

[Clark Lind]

I have placed the correct functions in the sheet you shared. My apologies! It didn't work because I didn't check to see what your locale is. I changed my sheet to Italy, and the functions were converted properly.
US Version: (uses commas ",")

Cell B4:     =transpose(query({Sheet1!A3:DT3},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Sheet1!C3:BL3);mod(column(Sheet1!C3:BL3)-column(Sheet1!C3),2)}),"Select Col1 where Col2=0"),"Col0")))))

Italian Version: (uses semicolons ";")

Cell B4:     =transpose(query({Sheet1!A3:DT3};"Select "&ArrayFormula(join(", ";text(query(transpose({column(Sheet1!C3:BL3);mod(column(Sheet1!C3:BL3)-column(Sheet1!C3);2)});"Select Col1 where Col2=0");"Col0")))))     

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/WdRNOGhJj5U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1ab8aa01-02f8-4a12-804a-0532604032ean%40googlegroups.com.

[massimo vigano]

Perfect! it works very well. But I have 25 people tabs with six month planning. At the end of week I delete the related data from "Planning".

How can I make automatic the work?