Macro help?

[shayalsamawi]

Hi All,

This is my first post - thanks so much in advance for helping.

I have data that looks like this:

Project	Subproject	Sam	Tom	Terry	Alice
Project 1	Subproject b	4​	1​		6​
Project 2	subproject d		5​	4​	5​
Project 3	subproject f	3​		3​	6​
Project 4	subproject q			4​

It's a time sheet with names of individuals and how many hours they spent on each task.

I need it to look like this:

sam	project 1	subproject b	4​
sam	project 3	subproject f	3​
Tom	Project 2	Subproject d	5​

so - removing any cells with no hours, the data is almost transposed with the name, project, subproject and hours.

I have to do this sheet weekly, which is why I wanted a macro to hopefully speed the process up.

I'd massively massively appreciate any help

Thanks
Shay

 

[lrobbo314]

Easy using Power Query instead of VBA.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Project	Subproject	Sam	Tom	Terry	Alice		Name	Project	Subproject	Hours Spent
2	Project 1	Subproject b	4	1		6		Sam	Project 1	Subproject b	4
3	Project 2	subproject d		5	4	5		Tom	Project 1	Subproject b	1
4	Project 3	subproject f	3		3	6		Alice	Project 1	Subproject b	6
5	Project 4	subproject q			4			Tom	Project 2	subproject d	5
6								Terry	Project 2	subproject d	4
7								Alice	Project 2	subproject d	5
8								Sam	Project 3	subproject f	3
9								Terry	Project 3	subproject f	3
10								Alice	Project 3	subproject f	6
11								Terry	Project 4	subproject q	4
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Subproject", type text}, {"Sam", type text}, {"Tom", type text}, {"Terry", type text}, {"Alice", type text}}),
    UOC = Table.UnpivotOtherColumns(Type, {"Project", "Subproject"}, "Name", "Hours Spent"),
    Reorder = Table.ReorderColumns(UOC,{"Name", "Project", "Subproject", "Hours Spent"})
in
    Reorder

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub shayalsamawi()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 4)
   For c = 3 To UBound(Ary, 2)
      For r = 2 To UBound(Ary)
         If Ary(r, c) <> "" Then
            nr = nr + 1
            Nary(nr, 1) = Ary(1, c)
            Nary(nr, 2) = Ary(r, 1)
            Nary(nr, 3) = Ary(r, 2)
            Nary(nr, 4) = Ary(r, c)
         End If
      Next r
   Next c
   Sheets("Sheet2").Range("A2").Resize(nr, 4).Value = Nary
End Sub

Change sheet names to suit.

 

[shayalsamawi]

Hi & welcome to MrExcel.
How about

Sub shayalsamawi()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
  
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 4)
   For c = 3 To UBound(Ary, 2)
      For r = 2 To UBound(Ary)
         If Ary(r, c) <> "" Then
            nr = nr + 1
            Nary(nr, 1) = Ary(1, c)
            Nary(nr, 2) = Ary(r, 1)
            Nary(nr, 3) = Ary(r, 2)
            Nary(nr, 4) = Ary(r, c)
         End If
      Next r
   Next c
   Sheets("Sheet2").Range("A2").Resize(nr, 4).Value = Nary
End Sub

Change sheet names to suit.

 

[shayalsamawi]

Hi & welcome to MrExcel.
How about

Sub shayalsamawi()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
  
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 4)
   For c = 3 To UBound(Ary, 2)
      For r = 2 To UBound(Ary)
         If Ary(r, c) <> "" Then
            nr = nr + 1
            Nary(nr, 1) = Ary(1, c)
            Nary(nr, 2) = Ary(r, 1)
            Nary(nr, 3) = Ary(r, 2)
            Nary(nr, 4) = Ary(r, c)
         End If
      Next r
   Next c
   Sheets("Sheet2").Range("A2").Resize(nr, 4).Value = Nary
End Sub

Change sheet names to suit.

Hi,

Thank you so much for this. You're officially my favourite person hahaha

It's not working - I'm not sure why.

When I click on the tab 'view' and then 'run macros' - it's not there?

Book123.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	12/09/2021
3
4	Sub Ledger
5	Sub Ledger	106324	6101274	106321	106553	171134	252888	6100650	119526	141816	6101053	273969	163932	6101224	6100537	6101203	106313
6		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
7		Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8	Person 9	Person 10	Person 11	Person 12	Person 13	Person 14	Person 15	Person 16
8	Sub Ledger	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Finished Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours
9	SM
10	PS
11	AS
12	CS
13	HD
14	A
15	L
16	B
17	T
18	S
19	TOIL
20	O
21	052L2, 10
22	052L3, 10		3	4	37
23	052L3, 20
24	052L1, 10
25	052L1, 20
26	052L1, 30
27	052L4, 10
28	052L21, 10
29	052L12, 130
30	052L13,10
31	052L20, 10
32	052L26, 10
33	052L27, 10
34	052L28, 10
35	052L14, 10
36	052L24,10
37	052L24,20
38	052L24,30
39	052L24,40
40	052L24,50
41	052L24,60
42	052L29, 10
43	052L30, 10
44	052L38, 10
45	052L31, 10
46	052L32, 10
47	052L33, 10
48	052L34, 10
49	052L35, 10
50	052L38, 10
51	052L37, 10
52	052J926
53	052J777
54	052J2750
55	052X002
56	052X013
57	052X218
58	052X902
59	052X898
60	052X945
61	052X784
62	052X981
63	052X973
64	052X1018
65
66	SUPP
67
68	052X890
69	052X1022
70	052X984
71	052X1021
72	052L36, 10
73	052X1033
74	052X1039
Sheet1

Cell Formulas
Range	Formula
D8:S8	D8	=IF(SUM(D9:D100)>36.99,"Finished Hours","Uncompleted Hours")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8:S8	Cell Value	contains "Finished Hours"	text	NO
I14	Cell Value	contains "Finished"	text	NO
D8:S8	Cell Value	contains "Uncompleted Hours"	text	NO
D9	Cell Value	contains ""Uncompleted Hours""	text	NO

 

[shayalsamawi]

Easy using Power Query instead of VBA.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Project	Subproject	Sam	Tom	Terry	Alice		Name	Project	Subproject	Hours Spent
2	Project 1	Subproject b	4	1		6		Sam	Project 1	Subproject b	4
3	Project 2	subproject d		5	4	5		Tom	Project 1	Subproject b	1
4	Project 3	subproject f	3		3	6		Alice	Project 1	Subproject b	6
5	Project 4	subproject q			4			Tom	Project 2	subproject d	5
6								Terry	Project 2	subproject d	4
7								Alice	Project 2	subproject d	5
8								Sam	Project 3	subproject f	3
9								Terry	Project 3	subproject f	3
10								Alice	Project 3	subproject f	6
11								Terry	Project 4	subproject q	4
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Subproject", type text}, {"Sam", type text}, {"Tom", type text}, {"Terry", type text}, {"Alice", type text}}),
    UOC = Table.UnpivotOtherColumns(Type, {"Project", "Subproject"}, "Name", "Hours Spent"),
    Reorder = Table.ReorderColumns(UOC,{"Name", "Project", "Subproject", "Hours Spent"})
in
    Reorder

Hi,

Thank you so much for this - I'm honestly so grateful for your kind help.

I can't do this - not sure why.

Thanks so so much again.

This is my data:

Book123.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
5	Sub Ledger	106324	6101274	106321	106553	171134	252888	6100650	119526	141816	6101053	273969	163932	6101224	6100537	6101203	106313
6		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
7		Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8	Person 9	Person 10	Person 11	Person 12	Person 13	Person 14	Person 15	Person 16
8	Sub Ledger	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Finished Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours
9	SM
10	PS
11	AS
12	CS
13	HD
14	A
15	L
16	B
17	T
18	S
19	TOIL
20	O
21	052L2, 10
22	052L3, 10		3	4	37
23	052L3, 20
24	052L1, 10
25	052L1, 20
26	052L1, 30
27	052L4, 10	2
28	052L21, 10							2
29	052L12, 130
30	052L13,10
31	052L20, 10
32	052L26, 10
33	052L27, 10
34	052L28, 10
35	052L14, 10
36	052L24,10
37	052L24,20
38	052L24,30
39	052L24,40
40	052L24,50
41	052L24,60
42	052L29, 10
43	052L30, 10
44	052L38, 10
45	052L31, 10
46	052L32, 10
47	052L33, 10
48	052L34, 10
49	052L35, 10
50	052L38, 10
51	052L37, 10
52	052J926
53	052J777
54	052J2750
55	052X002
56	052X013
57	052X218
58	052X902
59	052X898
60	052X945
61	052X784
62	052X981
63	052X973
64	052X1018
65
66	SUPP
67
68	052X890
69	052X1022
70	052X984
71	052X1021
72	052L36, 10
73	052X1033
74	052X1039
Sheet1

Cell Formulas
Range	Formula
D8:S8	D8	=IF(SUM(D9:D100)>36.99,"Finished Hours","Uncompleted Hours")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8:S8	Cell Value	contains "Finished Hours"	text	NO
I14	Cell Value	contains "Finished"	text	NO
D8:S8	Cell Value	contains "Uncompleted Hours"	text	NO
D9	Cell Value	contains ""Uncompleted Hours""	text	NO

 

[Fluff]

It's not working - I'm not sure why.

On reason is that your data bears no resemblance to what you originally posted.
Given the data you have now posted, what should the outcome be?

 

[shayalsamawi]

Hi Fluff,

Ahh - I'm sorry

Here's my data:

Book123.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Week Ending	12/09/2021
3
4		Project Description	Sub Ledger	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Finished Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours
5				Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8	Person 9	Person 10	Person 11	Person 12	Person 13
6		Project Description	Subledger	106324	6101274	106321	106553	171134	252888	6100650	119526	141816	6101053	273969	163932	6101224
7	Indirect Time	Admin - Sales + Marketing	SM
8		Admin - Product Support	PS
9		Admin - Applications Support	AS
10		Admin - Customer Support	CS
11		Customer Support (Help Desk)	HD
12		Admin - General	A						2	3
13		Leave	L
14		Bank Holiday	B
15		Training	T
16		Sick	S					1
17		Time Off in Lieu	TOIL
18		Other	O
19	Development Programme	PRODUCT SUPPORT - Pulsar/Firebird	052L2, 10
20		PRODUCT SUPPORT - Rockcore Product Support	052L3, 10		3	4	37
21		PRODUCT SUPPORT - Overburden	052L3, 20
22		PRODUCT SUPPORT - MQC	052L1, 10
23		PRODUCT SUPPORT - CEM	052L1, 20
24		PRODUCT SUPPORT - LEXMAR	052L1, 30
25		PRODUCT SUPPORT - MQR	052L4, 10	2
26		Hypersense Product Support	052L21, 10							2
27		Productionisation	052L12, 130
28		90 80MHz Halbach	052L13,10
29		Overburden Mk2	052L20, 10
30		MQC-R	052L26, 10
31		Q-Sense MQC	052L27, 10
32		Automation for X-Pulse	052L28, 10
33		AZUL	052L14, 10
34		BLOC - Work Package 1	052L24,10
35		BLOC - Work Package 2	052L24,20
36		BLOC - Work Package 3	052L24,30
37		BLOC - Work Package 4	052L24,40
38		BLOC - Work Package 5	052L24,50
39		BLOC - Work Package 6	052L24,60
40		Spinflow V3.1	052L29, 10
41		External lock for X-Pulse	052L30, 10
42		Switched Probe X-Pulse	052L38, 10
43		X-Pulse 80/Cost Down	052L31, 10
44		Halbach 20	052L32, 10
45		MQC HT	052L33, 10
46		Spin Studio Application Releases - Toothpaste	052L34, 10
47		Spin Echo (Grant)	052L35, 10
48		X-Pulse Probe	052L38, 10
49		Nottingham Trent University	052L37, 10
Sheet1

Cell Formulas
Range	Formula
D4:P4	D4	=IF(SUM(D7:D94)>36.99,"Finished Hours","Uncompleted Hours")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:S4	Cell Value	contains "Finished Hours"	text	NO
D4:S4	Cell Value	contains "Uncompleted Hours"	text	NO
D6:S6	Cell Value	contains "Finished Hours"	text	NO
I12	Cell Value	contains "Finished"	text	NO
D6:S6	Cell Value	contains "Uncompleted Hours"	text	NO
D7	Cell Value	contains ""Uncompleted Hours""	text	NO

I want it to look like this:

Book123.xlsm
	N	O	P
19	106324	052L3, 10	8
20	106324	052L1, 20	3
21	6101274	052L1, 20	4
Sheet2

So - person ID, project code (denoted on main sheet as subledger) and hours (missing any cells where there is no data). I can't thank you enough for your help - thanks so so much!!!

 

[shayalsamawi]

On reason is that your data bears no resemblance to what you originally posted.
Given the data you have now posted, what should the outcome be?

Hi Fluff,

Ahh - I'm sorry

Here's my data:

Book123.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Week Ending	12/09/2021
3
4		Project Description	Sub Ledger	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Finished Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours
5				Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8	Person 9	Person 10	Person 11	Person 12	Person 13
6		Project Description	Subledger	106324	6101274	106321	106553	171134	252888	6100650	119526	141816	6101053	273969	163932	6101224
7	Indirect Time	Admin - Sales + Marketing	SM
8		Admin - Product Support	PS
9		Admin - Applications Support	AS
10		Admin - Customer Support	CS
11		Customer Support (Help Desk)	HD
12		Admin - General	A						2	3
13		Leave	L
14		Bank Holiday	B
15		Training	T
16		Sick	S					1
17		Time Off in Lieu	TOIL
18		Other	O
19	Development Programme	PRODUCT SUPPORT - Pulsar/Firebird	052L2, 10
20		PRODUCT SUPPORT - Rockcore Product Support	052L3, 10		3	4	37
21		PRODUCT SUPPORT - Overburden	052L3, 20
22		PRODUCT SUPPORT - MQC	052L1, 10
23		PRODUCT SUPPORT - CEM	052L1, 20
24		PRODUCT SUPPORT - LEXMAR	052L1, 30
25		PRODUCT SUPPORT - MQR	052L4, 10	2
26		Hypersense Product Support	052L21, 10							2
27		Productionisation	052L12, 130
28		90 80MHz Halbach	052L13,10
29		Overburden Mk2	052L20, 10
30		MQC-R	052L26, 10
31		Q-Sense MQC	052L27, 10
32		Automation for X-Pulse	052L28, 10
33		AZUL	052L14, 10
34		BLOC - Work Package 1	052L24,10
35		BLOC - Work Package 2	052L24,20
36		BLOC - Work Package 3	052L24,30
37		BLOC - Work Package 4	052L24,40
38		BLOC - Work Package 5	052L24,50
39		BLOC - Work Package 6	052L24,60
40		Spinflow V3.1	052L29, 10
41		External lock for X-Pulse	052L30, 10
42		Switched Probe X-Pulse	052L38, 10
43		X-Pulse 80/Cost Down	052L31, 10
44		Halbach 20	052L32, 10
45		MQC HT	052L33, 10
46		Spin Studio Application Releases - Toothpaste	052L34, 10
47		Spin Echo (Grant)	052L35, 10
48		X-Pulse Probe	052L38, 10
49		Nottingham Trent University	052L37, 10
Sheet1

Cell Formulas
Range	Formula
D4:P4	D4	=IF(SUM(D7:D94)>36.99,"Finished Hours","Uncompleted Hours")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:S4	Cell Value	contains "Finished Hours"	text	NO
D4:S4	Cell Value	contains "Uncompleted Hours"	text	NO
D6:S6	Cell Value	contains "Finished Hours"	text	NO
I12	Cell Value	contains "Finished"	text	NO
D6:S6	Cell Value	contains "Uncompleted Hours"	text	NO
D7	Cell Value	contains ""Uncompleted Hours""	text	NO

I want it to look like this:

Book123.xlsm
	N	O	P
19	106324	052L3, 10	8
20	106324	052L1, 20	3
21	6101274	052L1, 20	4
Sheet2

So - person ID, project code (denoted on main sheet as subledger) and hours (missing any cells where there is no data). I can't thank you enough for your help - thanks so so much!!!

 

[Fluff]

This is absolutely nothing like what you originally asked for, so how do you expect any solution to work? All you have done is waste our time & yours.
Can you explain where those numbers in you output col P come from as they do not appear in your data.