Google Sheets - transpose column data in groups into rows

user_5862253 0
Viewed 582

I am trying to transpose data from column A to single rows. The original data has 3 rows for each name. But there could be either 1 or several jobs for each day. Each day needs to be treated separately, but this maybe best handled by manually adding at the beginning of each day.

This is for a fortnightly timesheet, therefore the number of rows in unpredictable.

The 1st image is my original data. the 2nd is the desired end result. enter image description here

The data is to transposed to any blank rows in columns b, c, d, e, as long as there are no blank rows, I can then reference them with my formulas. to place the information into the appropriate cells within the timesheet. I already have working formulas to do this.

the transpose section is what I need help with

enter image description here

Here is a link to my file https://docs.google.com/spreadsheets/d/1PhuFXDB2H1c9ua6szjhJEgJR91yXHhZAmn_2UGOBvIo/edit?usp=sharing

transpose google-sheets
3 Answers

try:

=ARRAYFORMULA({QUERY(IF(REGEXMATCH(A12:A, "\d+:\d+.*"), VLOOKUP(ROW(A12:A), 
 IF(IF(IFERROR(RIGHT(A12:A, 4)*1)>2000, A12:A, )<>"", {ROW(A12:A), 
 IF(IFERROR(RIGHT(A12:A, 4)*1)>2000, A12:A, )}), 2, 1), ), "where Col1 is not null"),
 QUERY(IF((IFERROR(RIGHT(A12:A, 4)*1)>2000)+(REGEXMATCH(A12:A, "\d+:\d+.*")),, A12:A), 
 "where Col1 is not null skipping 2"), 
 QUERY(QUERY(IF((IFERROR(RIGHT(A12:A, 4)*1)>2000)+(REGEXMATCH(A12:A, "\d+:\d+.*")),, A12:A), 
 "where Col1 is not null offset 1", 0), "skipping 2"), 
 FILTER(A12:A, REGEXMATCH(A12:A, "\d+:\d+.*"))})

enter image description here

user_56326290

If you want a solution with a formula in one cell only, try:

=arrayformula({"date","name","job type","start - end time";query(split(flatten(query(iferror(datevalue(A12:A),),"where Col1 is not null",0)&split(flatten(split(textjoin(char(9999),1,if(regexmatch(to_text(A12:A),":+.*-+"),A12:A&char(9998),if(iferror(datevalue(A12:A),)<>"",char(10001),A12:A))),char(10001))),char(9998))),char(9999)),"where Col2 is not null",0)})

enter image description here

user_157844150

in put B12 :

=IFERROR(if(FIND("2021",A12)>=1,0,""),B11+1)

in put C12 :

=if(B12=0,A12,C11)

in D12 :

=if(mod(B12,3)=1,TRANSPOSE(A12:A14),"")

and drag downwards. Then in F12 put :

=FILTER(C:F,F:F<>"")

and drag downwards.

Idea : use transpose() with a conditional counter. use (F12)filter to remove blank.

Please if it works/understandable/not.

user_76024680
Related