How can I transpose from a column to rows an employee timesheet data

user_5707197 0
Viewed 53

I have a employee timesheet data in 1 column and need to transpose this to lines and columns.

Original data with 1 column

mon

02/05/2022

9:04

12:47

13:52

18:09

-

-

tue

03/05/2022

9:13

13:03

14:06

18:10

-

-

wed

04/05/2022

8:58

12:57

14:08

18:19

-

-

thu

05/05/2022

8:52

12:26

13:32

17:39

-

-

fri

06/05/2022

10:13

13:10

14:15

18:14

-

-

sat

07/05/2022

-

-

-

-

-

-

sun

08/05/2022

-

-

-

-

-

-

mon

09/05/2022

8:49

12:46

13:48

18:11

-

-

tue

10/05/2022

8:52

12:51

13:55

18:07

-

-

Transposed data to table with lenght 8 columns

mon 02/05/2022  9:04    12:47   13:52   18:09   -   -

tue 03/05/2022  9:13    13:03   14:06   18:10   -   -

wed 04/05/2022  8:58    12:57   14:08   18:19   -   -

thu 05/05/2022  8:52    12:26   13:32   17:39   -   -

fri 06/05/2022  10:13   13:10   14:15   18:14   -   -

sat -   -   -   -   -   -   -

sun -   -   -   -   -   -   -

mon 09/05/2022  8:49    12:46   13:48   18:11   -   -

tue 10/05/2022  8:52    12:51   13:55   18:07   -   -

How can I do it? I tryed with =TRANSPOSE(QUERY functions but didn't got what I expected.

Thank you!

flatten transpose google-sheets filter arrays
2 Answers

try:

={FILTER(A:A, MOD(ROW(A:A)-1, 8)=0), 
  FILTER(A:A, MOD(ROW(A:A)-2, 8)=0), 
  FILTER(A:A, MOD(ROW(A:A)-3, 8)=0), 
  FILTER(A:A, MOD(ROW(A:A)-4, 8)=0), 
  FILTER(A:A, MOD(ROW(A:A)-5, 8)=0), 
  FILTER(A:A, MOD(ROW(A:A)-6, 8)=0), 
  FILTER(A:A, MOD(ROW(A:A)-7, 8)=0), 
  FILTER(A:A, MOD(ROW(A:A)-8, 8)=0)}

enter image description here

user_56326290

try:

={QUERY(A1:A, "skipping 8", ), 
  QUERY(A2:A, "skipping 8", ), 
  QUERY(A3:A, "skipping 8", ), 
  QUERY(A4:A, "skipping 8", ), 
  QUERY(A5:A, "skipping 8", ), 
  QUERY(A6:A, "skipping 8", ), 
  QUERY(A7:A, "skipping 8", ), 
  QUERY(A8:A, "skipping 8", )}

enter image description here

or:

={QUERY(A:A, "skipping 8", ), 
  QUERY(QUERY(A:A, " offset 1", ), "skipping 8", ), 
  QUERY(QUERY(A:A, " offset 2", ), "skipping 8", ), 
  QUERY(QUERY(A:A, " offset 3", ), "skipping 8", ), 
  QUERY(QUERY(A:A, " offset 4", ), "skipping 8", ), 
  QUERY(QUERY(A:A, " offset 5", ), "skipping 8", ), 
  QUERY(QUERY(A:A, " offset 6", ), "skipping 8", ), 
  QUERY(QUERY(A:A, " offset 7", ), "skipping 8", )}

enter image description here

user_56326290
Related