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!
CodePudding user response：
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)}
CodePudding user response：
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", )}
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", )}
