'Arrange downloaded data into more useful way in google sheets
We currently have a fixed report data that we can only manipulate after download and to simplify, it looks like this:
raw report data extracted to google sheets
a b c
1 Start Date Time Adhering to Schedule (Hours) Time Not Adhering to Schedule (Hours)
2 Employee: A Supervisor: X
3 5/4/2022 7.65 1.35
4 5/5/2022 8.12 0.88
5 5/6/2022 6.95 2.05
6 5/9/2022 8.7 0.3
7 5/10/2022 7.45 1.55
8 5/11/2022 8.63 0.37
9 5/12/2022 8.08 0.92
10 5/13/2022 6.13 0.13
11 Totals: 61.71 7.55
12 Employee: B Supervisor: X
13 5/1/2022 3.8 0.27
14 5/2/2022 6.72 2.28
15 5/3/2022 6.1 2.9
16 5/4/2022 8.43 0.57
17 5/5/2022 5.85 0.53
18 5/10/2022 6.13 2.87
19 5/11/2022 0 1.5
20 5/12/2022 2 1.5
21 5/13/2022 1.75 1.75
22 Totals: 40.78 14.17
I would like some help in constructing a new sheet via formulas so that it rearranges the raw data as follows:
desired output
a b c d e
1 EMPLOYEE SUPERVISOR Start Date Time Adhering to Schedule (Hours) Time Not Adhering to Schedule (Hours)
2 A X 04/05/22 7.65 1.35
3 A X 05/05/22 8.12 0.88
4 A X 06/05/22 6.95 2.05
5 A X 09/05/22 8.70 0.30
6 A X 10/05/22 7.45 1.55
7 A X 11/05/22 8.63 0.37
8 A X 12/05/22 8.08 0.92
9 A X 13/05/22 6.13 0.13
10 B X 01/05/22 3.80 0.27
11 B X 02/05/22 6.72 2.28
12 B X 03/05/22 6.10 2.90
13 B X 04/05/22 8.43 0.57
14 B X 05/05/22 5.85 0.53
15 B X 10/05/22 6.13 2.87
16 B X 11/05/22 0.00 1.50
17 B X 12/05/22 2.00 1.50
18 B X 13/05/22 1.75 1.75
It probably needs some combination of QUERY() ARRAYFORMULA(), TRANSPOSE() and/or INDEX() or something.. but i can't quite figure it out. I need some help with to get started in the right track. the dates and data between employees are dynamic so the formula in the desired result needs to adjust to that as well.
thanks!
edit: adding a sample trix for reference :) https://docs.google.com/spreadsheets/d/1m_FCGcnXvnEiMZ8X4K1eEsMljORWV4V1Yq_81vFnx4Y/edit?usp=sharing
Solution 1:[1]
Gobal solution
in E1
={ArrayFormula(if(A1:A="Totals:",,{
substitute(lookup(row(A1:A),row(A1:A)/if(ISNUMBER(A1:A),0,1),A1:A),"Employee: ",""),
substitute(lookup(row(A1:A),row(A1:A)/if(ISNUMBER(A1:A),0,1),C1:C),"Supervisor: ","")
})),Arrayformula(if(ISNUMBER(A1:A),{A1:A,B1:B,C1:C},))}
In 3 steps (3 arrayformulas),
try in H1
=arrayformula(if(left(A1:A,6)="Totals",,if(left(A1:A,8)="Employee",{B1:B,D1:D,E1:E,E1:E,E1:E},{E1:E,E1:E,A1:A,B1:B,C1:C})))
then, back in F1 to complete all rows with employee and supervisor
=ArrayFormula({lookup(row(H:H),row(H:H)/if(H:H<>"",1,0),H:H),lookup(row(I:I),row(I:I)/if(I:I<>"",1,0),I:I)})
finally, if you want to reduce the presentation, in M1
=query(F:L,"select F,G,J,K,L where J is not null",0)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |