VBA Code Help

[markster]

I've got a massive scheduling job to do from a spreadsheet with about 1300 lines and could really do with some VBA code to help me.

There are three sheets:

SHEET 1 - Details of working patterns - data is contained in columns A to W but I've hidden some columns so the screen shot isn't too big

SHEET 2 - Individual Weeks, Dates and Days of the week

SHEET 3 - The summary I need to be created by the Macro from Sheets 1 & 2

In order to create the summary I need in SHEET 3, it needs to look at 2 key columns in SHEET 1 (Column G - Week Range & Column Q Day Range) and Columns - A-F in SHEET 2. In the summary, it splits out out the week numbers into separate lines with actual dates and includes all data from SHEET 1 (I mean entire row of data A to W). I could try to explain it more in words but I think looking at the SHEET 3 screenshot illustrates what I need and trying to explain further might cause confusion. Obviously, I'm happy to answer any questions you may have.

SHEET 1 - Details of working patterns - data is contained in columns A to W but I've hidden some columns so the screen shot isn't too big

Sheet 2 - Individual Weeks, Dates and Days of the week

Sheet 3 - The summary I need to be created by the Macro from Sheets 1 & 2 (Desired Output)

Thanks
Mark

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[bobsan42]

Actually, everything you need is to expand the week numbers in col. G on sheet 1, then convert them to dates based on the starting date of week 1.
You don't really need all these dates in sheet 2.
Potentially - it is possible to skip a week, in this case it may be good to have a list of the first day of the week. But in your case I see not disruptions in the dates flow.
Maybe something similar to this may help. And it doesn't require a macro:
Excel - Expand IP address range to individual IP ?

 

[markster]

Ok thanks will take a look.

 

[bobsan42]

First I would make the same remark as, mumps above.
Second: I will present a solution using PowerQuery and m language.
The setup is slightly different than yours:

...............................xlsm
		B	C	D			G		I						O	P	Q	R	S	T			W
1
2		Activity Code	Column1	Activity			WeekRange(s)		Shift Worker						No in Room	Room	Day	Start time	Finish time	Duration			Reference
3		B24546		office			1-3,6,9		John Smith						26	405	Wednesday	09:00	17:00	08:00			135464
4		A789465		Warehouse			1-6,15,20		Jane Brown						23	345	Thursday	08:00	16:00	08:00			153464
5		C7543		Duty cover			26		Paul Oakley						23	222	Friday	10:00	18:00	08:00			2546
6
7
8
Sheet1

Cell Formulas
Range	Formula
T3:T5	T3	=S3-R3

The data on sheet1 must turn into a table called for example Table1.

Sheet2 contains two tables only:

....................................xlsm
	A	B	C
1	Table:	Table5
2		Week 1 Start date
3		27.09.2021
4
5	Table:	tbWeekdays
6		Day	Num
7		Monday	0
8		Tuesday	1
9		Wednesday	2
10		Thursday	3
11		Friday	4
12		Saturday	5
13		Sunday	6
Sheet2

Then you need to create 3 Data sets and load them as Connections only using PowerQuery:
Table1:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in
    Source

tbWeekdays:

let
    Source = Excel.CurrentWorkbook(){[Name="tbWeekdays"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Num", Int64.Type}})
in
    #"Changed Type"

StartWeek: if you change the startweek date then the Result dates will change after refresh.

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week 1 Start date", Int64.Type}}),
    #"Week 1 Start date" = #"Changed Type"{0}[Week 1 Start date]
in
    #"Week 1 Start date"

When this is all prepared you can now create a blank PowerQuery, paste in the following code in the advanced editor (or use it as guidance), sort out any differences in column names etc. and load the result to sheet3:

let
    Source = Table.NestedJoin(Table1, {"Day"}, tbWeekdays, {"Day"}, "tbWeekdays", JoinKind.LeftOuter),
    sn = StartWeek,
    #"Expanded tbWeekdays" = Table.ExpandTableColumn(Source, "tbWeekdays", {"Num"}, {"Num"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Expanded tbWeekdays", {{"WeekRange(s)", type text}}, "bg-BG"), {{"WeekRange(s)", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "WeekRange(s)"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"WeekRange(s)", type text}, {"Duration", type time}, {"Finish time", type time}, {"Start time", type time}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "WeekRange(s)", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"WR1", "WR2"}),
    typechange1= Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"WR1", Int64.Type}, {"WR2", Int64.Type}}),
    replace1 = Table.ReplaceValue(typechange1,null, each _[WR1],Replacer.ReplaceValue,{"WR2"}),
    listnums = Table.AddColumn(replace1,"CustomList",each List.Numbers([WR1]-1,[WR2]-[WR1]+1)),
    expandList = Table.ExpandListColumn(listnums, "CustomList"),
    #"Added Custom" = Table.AddColumn(expandList, "Custom", each [CustomList]*7+[Num]+sn),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Col1", "Activity Code", "Column1", "Activity", "Column2", "Column3", "Date", "WR1", "WR2", "Column4", "Shift Worker", "Zone", "Column5", "Column6", "Column7", "Column8", "No in Room", "Room", "Day", "Start time", "Finish time", "Duration", "Column9", "Column10", "Reference", "Num", "CustomList"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"WR1", "WR2", "Num", "CustomList"})
in
    #"Removed Columns"

...................................xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Col1	Activity Code	Column1	Activity	Column2	Column3	Date	Column4	Shift Worker	Zone	Column5	Column6	Column7	Column8	No in Room	Room	Day	Start time	Finish time	Duration	Column9	Column10	Reference
2	a	B24546		office			29.09.2021		John Smith						26	405	Wednesday	09:00:00	17:00:00	08:00:00			135464
3	a	B24546		office			06.10.2021		John Smith						26	405	Wednesday	09:00:00	17:00:00	08:00:00			135464
4	a	B24546		office			13.10.2021		John Smith						26	405	Wednesday	09:00:00	17:00:00	08:00:00			135464
5	a	B24546		office			03.11.2021		John Smith						26	405	Wednesday	09:00:00	17:00:00	08:00:00			135464
6	a	B24546		office			24.11.2021		John Smith						26	405	Wednesday	09:00:00	17:00:00	08:00:00			135464
7		A789465		Warehouse			30.09.2021		Jane Brown						23	345	Thursday	08:00:00	16:00:00	08:00:00			153464
8		A789465		Warehouse			07.10.2021		Jane Brown						23	345	Thursday	08:00:00	16:00:00	08:00:00			153464
9		A789465		Warehouse			14.10.2021		Jane Brown						23	345	Thursday	08:00:00	16:00:00	08:00:00			153464
10		A789465		Warehouse			21.10.2021		Jane Brown						23	345	Thursday	08:00:00	16:00:00	08:00:00			153464
11		A789465		Warehouse			28.10.2021		Jane Brown						23	345	Thursday	08:00:00	16:00:00	08:00:00			153464
12		A789465		Warehouse			04.11.2021		Jane Brown						23	345	Thursday	08:00:00	16:00:00	08:00:00			153464
13		A789465		Warehouse			06.01.2022		Jane Brown						23	345	Thursday	08:00:00	16:00:00	08:00:00			153464
14		A789465		Warehouse			10.02.2022		Jane Brown						23	345	Thursday	08:00:00	16:00:00	08:00:00			153464
15		C7543		Duty cover			25.03.2022		Paul Oakley						23	222	Friday	10:00:00	18:00:00	08:00:00			2546
Sheet3

 

[markster]

Thanks for your feedback everyone. I've never heard of power query before so will get on top with that. I'll look at all this in more detail this afternoon.

Thanks again.
Mark

 

[markster]

Hi @bobsan42 I'm struggling with this a bit as never even heard of powerquery until your reply above. I've set up the tables as you directed. Now I'm not too sure how to load the connections and how to produce the summary - i've looked on youtube and will continue to try and figures this out but any help you can give would be much appreciated. All the best Mark

 

[markster]

Hi @bobsan42 SORRY IGNORE THE POST ABOVE AS I POSTED BEFORE FINISHING IT AND I COULDN'T EDIT I'm struggling with this a bit as never even heard of powerquery until your reply above.

Also, the data in sheet 1 is now going to separated into different workbooks that need to pull through and combine on sheet 3. So there will be multiple workbooks that have identical columns to the data in sheet 1 - lets call them workbook1, workbook2, workbook3, workbook 4 - Sheet 2 (days of the week) and Sheet 3 - the final summary will be in a workbook call Master Scheduling Summary. Not sure how to update the code to reflect this.

I've also set up the tables as you directed but not too sure how to load the connections and how to produce the summary - i've looked on youtube and will continue to try and figures this out but any help you can give would be much appreciated. All the best Mark

 

[bobsan42]

I came up with something. Maybe not ideal, but it only takes one query.
First Pick up a cell in your workbook, name it nmStart (Scope: Workbook) and fill in the starting date of of the first week maybe 27-09-2021 in your case.
The data in wb1, 2, 3 4 should have headers and start from the beginning of the sheets.
1. Then goto Data>Get Data>From other sources>Blank Query
2. Press Advanced Editor in the PowerQuery Editor
3. Clear all the text in the window and paste the following code

let
    wdays = Table.TransformColumnTypes(Table.FromRows({{"Monday",0},{"Tuesday",1},{"Wednesday",2},{"Thursday",3},{"Friday",4},{"Saturday",5},{"Sunday",6}},{"Day","Num"}),{{"Day", type text}, {"Num", Int64.Type}}),
    sn = Number.From(Excel.CurrentWorkbook(){[Name="nmStart"]}[Content]{0}[Column1]),

    src1 = Table.PromoteHeaders(Excel.Workbook(File.Contents("[COLOR=rgb(226, 80, 65)]C:\Users\...\Desktop\Workbook1.xlsx[/COLOR]"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    src2 = Table.PromoteHeaders(Excel.Workbook(File.Contents("[COLOR=rgb(226, 80, 65)]C:\Users\...\Desktop\Workbook2.xlsx[/COLOR]"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    src3 = Table.PromoteHeaders(Excel.Workbook(File.Contents("[COLOR=rgb(226, 80, 65)]C:\Users\...\Desktop\Workbook3.xlsx[/COLOR]"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    src4 = Table.PromoteHeaders(Excel.Workbook(File.Contents("[COLOR=rgb(226, 80, 65)]C:\Users\...\Desktop\Workbook4.xlsx[/COLOR]"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    AllSource = Table.Combine({src1,src2,src3,src4}),

    Source = Table.NestedJoin(AllSource, {"Day"}, wdays, {"Day"}, "tbWeekdays", JoinKind.LeftOuter),
    #"Expanded tbWeekdays" = Table.ExpandTableColumn(Source, "tbWeekdays", {"Num"}, {"Num"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Expanded tbWeekdays", {{"WeekRange(s)", type text}}, "bg-BG"), {{"WeekRange(s)", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "WeekRange(s)"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"WeekRange(s)", type text}, {"Duration", type time}, {"Finish time", type time}, {"Start time", type time}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "WeekRange(s)", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"WR1", "WR2"}),
    typechange1= Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"WR1", Int64.Type}, {"WR2", Int64.Type}}),
    replace1 = Table.ReplaceValue(typechange1,null, each _[WR1],Replacer.ReplaceValue,{"WR2"}),
    listnums = Table.AddColumn(replace1,"CustomList",each List.Numbers([WR1]-1,[WR2]-[WR1]+1)),
    expandList = Table.ExpandListColumn(listnums, "CustomList"),
    #"Added Custom" = Table.AddColumn(expandList, "Custom", each [CustomList]*7+[Num]+sn),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Col1", "Activity Code", "Column1", "Activity", "Column2", "Column3", "Date", "WR1", "WR2", "Column4", "Shift Worker", "Zone", "Column5", "Column6", "Column7", "Column8", "No in Room", "Room", "Day", "Start time", "Finish time", "Duration", "Column9", "Column10", "Reference", "Num", "CustomList"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"WR1", "WR2", "Num", "CustomList"})
in
    #"Removed Columns"

4. You will need to change the workbooks names to match your setup, possibly sheet names
5. You will probabbly have mismatching column names which you will have to correct (since you have not provided all column names I used names like Column1, Column2, etc.)
6. Once finished press Done to return to the PQ Editor
7. If there are errors you will see a yellow box saying so. You have to follow through and resolve all errors (probably through the Advanced editor)
8. When succeeded you will see your final table with the data.
9. Press Close & Load To ... and select where the data is to be loaded e.g. Sheet 3 or a new sheet.


1.

2.

9.

 

[markster]

Thanks @bobsan42 and thanks for introducing me to power query I have so many potential uses for it - it's brilliant and how did I not know about it?! I'll work through the additional stuff you sent this afternoon. Thanks again.