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

 

[shayalsamawi]

Hi Fluff,

I didn't mean to - I really thought the concept was similar enough,I didn't know about the XL2BB at the time of posting...it's okay - i will find a solution elsewhere.

For info - output P was just an example of hours - they aren't on data - I just wanted to show what I'd want it to look like once the timesheet is populated.

I apologise for wasting your time - I had no intention of this.

Thanks again anyway

Shay

 

[Fluff]

You do not need need to look elsewhere, but before I write the new code I would like to see exactly what you are after from the data you showed, otherwise I'm just guessing.

 

[shayalsamawi]

You do not need need to look elsewhere, but before I write the new code I would like to see exactly what you are after from the data you showed, otherwise I'm just guessing.

Hi Fluff,

Thanks again for your help.

I hope this is clear - I've tried to make it as clear as possible (as I should have in the beginning).

This is my whole spreadsheet:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
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	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	Person 14	Person 15	Person 16
6		Project Description	Subledger	106324	6101274	106321	106553	171134	252888	6100650	119526	141816	6101053	273969	163932	6101224	6100537	6101203	106313
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
50	Project Build Time	VDvG	052J926
51		Copenhagen	052J777
52		Dupont Autosampler	052J2750
53	Installations	MQR20-IFPEN, France	052X002
54		Geospec 12-50 and 2-100 - SONATRACH	052X013
55		Geospec 12-50 and 2-75 - KAUST	052X218
56		Munich PM visit	052X902
57		ESSO MQC to MQC+ upgrade	052X898
58		X-Pulse installation at Heriot-Watt University - 052S573 - 4 days on site	052X945
59		DuPont MQC+ MQ-Auto Installation (preparation)	052X784
60		University of Oxford glove box move (initial survey)	052X981
61		Installation and training at CBRN Romania 052S650	052X973
62		Lulea University of Technology Sweden (052S789)	052X1018
63	Service	FOC Customer Support
64		Support Contracts	SUPP
65		Munster University Pulsar Repair and Upgrade to Win 10
66		Repair to PUL1026 - Germany (Rototec-Spintec)	052X890
67		Holme House Repair Visit	052X1022
68		ONGC CEWELL remote PM	052X984
69		Minakem Pulsar Assessment + Repair	052X1021
70		Toulouse Upgrade and Repair	052L36, 10
71		United Utilities MQC+ Autosampler PM	052X1033
72		Koura Global (Mexichem) PM	052X1039
Sheet1

Cell Formulas
Range	Formula
D4:S4	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

This is a zoom in of the first section as an example of what I mean:

Book1
	C	D	E	F	G	H
6	Subledger	106324	6101274	106321	106553	171134
7	SM
8	PS
9	AS
10	CS
11	HD
12	A
13	L
14	B
15	T
16	S					1
17	TOIL
18	O
19	052L2, 10
20	052L3, 10		3	4	37
21	052L3, 20
22	052L1, 10
23	052L1, 20
24	052L1, 30
25	052L4, 10	2
26	052L21, 10
27	052L12, 130
28	052L13,10
29	052L20, 10
30	052L26, 10
Sheet1

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

I would like the data to look like this, leaving out where there is 0 hours worked on a project.

Book1
	L	M	N
7	106324	052L4, 10	2
8	6101274	052L3, 10	3
9	106321	052L3, 10	4
10	106553	052L3, 10	37
Sheet2

So - showing, the person ID, the subledger code and the hours worked on that specific project.

I hope this is clear.

Thanks again

 

[Fluff]

Thanks for that, are you only interested in the hours worked from row 19 down, or do you also want the hours from rows 7 to 18?

 

[shayalsamawi]

No - apologies - this was only for an example. I'd like them all, spanning all 16 employees (column D to S) and all projects (row 7 to row 72)

 

[Fluff]

Ok, how about

Sub shayalsamawi()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   With Sheets("Sheet1")
      Ary = .Range("C6:S" & .Range("C" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 3)
   For c = 2 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, c)
         End If
      Next r
   Next c
   Sheets("Sheet2").Range("A2").Resize(nr, 3).Value = Nary
End Sub

 

[shayalsamawi]

Thanks for that, are you only interested in the hours worked from row 19 down, or do you also want the hours from rows 7 to

Ok, how about

Sub shayalsamawi()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
  
   With Sheets("Sheet1")
      Ary = .Range("C6:S" & .Range("C" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 3)
   For c = 2 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, c)
         End If
      Next r
   Next c
   Sheets("Sheet2").Range("A2").Resize(nr, 3).Value = Nary
End Sub

Thank you so so much - this is exactly what I was after, works perfectly. Thanks so much

 

[Fluff]

You're welcome & thanks for the feedback.