• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Travel Schedule

bretsh

New Member
Gidday,

First time poster, and seeking some help. Unsure if this can be achieved through formula's or if it requires VBA scripting (Im inexperienced VBA user) or whether it exceeds the capacity of Excel (albeit I have seen some very cool spreadsheets on the Web)

I have a worksheet, broken down to employees and dates they are travelling in this case to other countries however would be looking to incoporate inter-state travel.

69513

On the second worksheet I have a schedule of when they are or were away. (modified version of get date ranges from a schedule)

69514

Where I come unstuck is if there is no distinct break between dates. Miss Young was in USA then England it pulls the inital fine but it crashes out when it comes to an over lap on location. Same if they are in location for one day.

Whilst I have attached the spreadsheet, I am only working with four staff members on this one while I try make it, work, however the company can potentially have upto 6000 employees on here with date ranges of upto six months to a year. (whether using formulas to capture or vba, I am keen to learn new tricks)

Any help and advice would be fantastic.
 

Attachments

AliGW

Active Member
Which version of Excel do you have?

PowerQuery is made for this.

M Code:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Staff Id", Int64.Type}, {"Title", type text}, {"Name", type text}, {"Section", type text}, {"Full/Part Time", type text}, {"Gender", type text}, {"23/11/2019", type any}, {"24/11/2019", type any}, {"25/11/2019", type any}, {"26/11/2019", type any}, {"27/11/2019", type text}, {"28/11/2019", type text}, {"29/11/2019", type text}, {"30/11/2019", type text}, {"01/12/2019", type text}, {"02/12/2019", type text}, {"03/12/2019", type text}, {"04/12/2019", type text}, {"05/12/2019", type text}, {"06/12/2019", type text}, {"07/12/2019", type text}, {"08/12/2019", type text}, {"09/12/2019", type text}, {"10/12/2019", type any}, {"11/12/2019", type any}, {"12/12/2019", type any}, {"13/12/2019", type any}, {"14/12/2019", type any}, {"15/12/2019", type any}, {"16/12/2019", type any}, {"17/12/2019", type any}, {"18/12/2019", type any}, {"19/12/2019", type any}, {"20/12/2019", type any}, {"21/12/2019", type any}, {"22/12/2019", type any}, {"23/12/2019", type any}, {"24/12/2019", type any}, {"25/12/2019", type any}, {"26/12/2019", type any}, {"27/12/2019", type any}, {"28/12/2019", type any}, {"29/12/2019", type any}, {"30/12/2019", type any}, {"31/12/2019", type any}, {"01/01/2020", type any}, {"02/01/2020", type any}, {"03/01/2020", type any}, {"04/01/2020", type any}, {"05/01/2020", type any}, {"06/01/2020", type any}, {"07/01/2020", type any}, {"08/01/2020", type any}, {"09/01/2020", type any}, {"10/01/2020", type any}, {"11/01/2020", type any}, {"12/01/2020", type any}, {"13/01/2020", type any}, {"14/01/2020", type any}, {"15/01/2020", type any}, {"16/01/2020", type any}, {"17/01/2020", type any}, {"18/01/2020", type any}, {"19/01/2020", type any}, {"20/01/2020", type any}, {"21/01/2020", type any}, {"22/01/2020", type any}, {"23/01/2020", type any}, {"24/01/2020", type any}, {"25/01/2020", type any}, {"26/01/2020", type any}, {"27/01/2020", type any}, {"28/01/2020", type any}, {"29/01/2020", type any}, {"30/01/2020", type any}, {"31/01/2020", type any}, {"01/02/2020", type any}, {"02/02/2020", type any}, {"03/02/2020", type any}, {"04/02/2020", type any}, {"05/02/2020", type any}, {"06/02/2020", type any}, {"07/02/2020", type text}, {"08/02/2020", type text}, {"09/02/2020", type text}, {"10/02/2020", type text}, {"11/02/2020", type any}, {"12/02/2020", type any}, {"13/02/2020", type text}, {"14/02/2020", type text}, {"15/02/2020", type text}, {"16/02/2020", type any}, {"17/02/2020", type text}, {"18/02/2020", type text}, {"19/02/2020", type text}, {"20/02/2020", type any}, {"21/02/2020", type any}, {"22/02/2020", type any}, {"23/02/2020", type any}, {"24/02/2020", type any}, {"25/02/2020", type any}, {"26/02/2020", type any}, {"27/02/2020", type any}, {"28/02/2020", type any}, {"29/02/2020", type any}, {"01/03/2020", type any}, {"02/03/2020", type any}, {"03/03/2020", type any}, {"04/03/2020", type any}, {"05/03/2020", type any}, {"06/03/2020", type any}, {"07/03/2020", type any}, {"08/03/2020", type any}, {"09/03/2020", type any}, {"10/03/2020", type any}, {"11/03/2020", type any}, {"12/03/2020", type any}, {"13/03/2020", type any}, {"14/03/2020", type any}, {"15/03/2020", type any}, {"16/03/2020", type any}, {"17/03/2020", type any}, {"18/03/2020", type any}, {"19/03/2020", type any}, {"20/03/2020", type any}, {"21/03/2020", type any}, {"22/03/2020", type any}, {"23/03/2020", type any}, {"24/03/2020", type any}, {"25/03/2020", type any}, {"26/03/2020", type any}, {"27/03/2020", type any}, {"28/03/2020", type any}, {"29/03/2020", type any}, {"30/03/2020", type any}, {"31/03/2020", type any}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Staff Id", "Title", "Name", "Section", "Full/Part Time", "Gender"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Staff Id", "Title", "Name", "Section", "Full/Part Time", "Gender", "Value"}, {{"Start", each List.Min([Attribute]), type text}, {"End", each List.Max([Attribute]), type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Staff Id", "Title", "Name", "Section", "Full/Part Time", "Gender", "Start", "End", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "Location"}})
in
    #"Renamed Columns"
 

Attachments

Last edited:

bretsh

New Member
Evening AliGW,

thank you for the quick turn around, I’m working with both excel 2016 & 2019. I have never heard of power query, and will be looking it up as soon as I get back to the office.

looks very promising based on your excel document.
 

AliGW

Active Member
It is in 2016 and 2019 - it is called Get & Transform and is on the Data ribbon. You said you were keen to learn new tricks! :D
 

bretsh

New Member
Afternoon AliGW,

Thank you, have played around now with Power Query, took a little bit initially to work out how to set up, however have conquered that little piece. The only thing I am now trying to work out is if staff have overlapping locations within a block period, i.e. went to the USA, NZ, AUS, USA how to cover the specific dates they were in location as opposed to covering the whole period (screenshot below):

69560

Power query table (very cool trick thank you..)

69561

See start date for Mr Green is the commencement of the second period of NZ travel, whilst the end date reflects the end of first travel to NZ.

Just unsure who within the query to design it to identify exact date range, 23 Nov 19 - 26 Nov 19 - NZ, 27 Nov 19 - 29 Nov 19 - USA, 30 Nov 19 - 02 Dec 19 - NORWAY, 03 Dec 19 - 05 Dec 19 NZ.

Its probably something really simple, I just can't see it....

Any advice would be greatly appreciated.
 

Attachments

AlanSidman

Well-Known Member
This is a Pivot Table developed using Power PIvot. Does this presentation work for you.

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
21
05 DETitleNameStartDEndDCountry
22
12753
MrSmith,Mark01/12/201930/11/2019NZ
23
14859
MrGreen,Nicholas03/12/2019,27/11/2019,01/12/201926/11/2019,29/11/2019,30/11/2019NZ,USA,NORWAY
24
28891
MrBlake,Robert01/12/2019,02/12/201930/11/2019,03/12/2019NORWAY,SWEDEN
25
38154
MissYoung,Melissa04/12/2019,06/12/201905/12/2019,07/12/2019USA,ENG
Sheet: Staff Travel Tracker
 

bretsh

New Member
Afternoon Alan,

Thank you for the response, Pivot Table may work, and I can see how just curious if it can be broken down by row to reflect a new row for each differing location?
 

AliGW

Active Member
Sorry - I can't visualise what you are asking for and how it differs from what I gave you. Can you mock it up manually? It can probably be done, but I need to know what I am aiming for.
 

bretsh

New Member
Afternoon AliGW,

Sorry for my poor explanation of the issue.

Current screen shot, which shows how it is broken down utilising the power query based on a group and then Min and Max dates (albeit it is intersting that in same cases if dates overlap the month it reverses them).
69575

And wondering whether it is possible to portray like this

69574

Again thanking you for taking the time to help with this issue, and the teaching of new tricks.
 

AliGW

Active Member
Yes - got it!

Have a look at the attached - is that what you want? I made use of the GroupKind.Local attribute.

Code:
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Staff Id", "Title", "Name", "Section", "Full/Part Time", "Gender", "Value"}, {{"Start", each List.Min([Attribute]), type text}, {"End", each List.Max([Attribute]), type text}}, GroupKind.Local
),
Staff IdTitleNameSectionFull/Part TimeGenderStartEndLocation
14859​
MrGreen,NicholasSalesFTM
27/11/2019​
30/11/2019​
NZ
12753​
MrSmith,MarkSalesPTM
29/11/2019​
02/12/2019​
NZ
28891​
MrBlake,RobertHRFTM
30/11/2019​
01/12/2019​
NORWAY
28891​
MrBlake,RobertHRFTM
02/12/2019​
03/12/2019​
SWEDEN
14859​
MrGreen,NicholasSalesFTM
03/12/2019​
05/12/2019​
USA
38154​
MissYoung,MelissaProductionFTF
04/12/2019​
05/12/2019​
USA
38154​
MissYoung,MelissaProductionFTF
06/12/2019​
07/12/2019​
ENG
14859​
MrGreen,NicholasSalesFTM
07/12/2019​
09/12/2019​
NORWAY
14859​
MrGreen,NicholasSalesFTM
07/02/2020​
10/02/2020​
NZ
14859​
MrGreen,NicholasSalesFTM
13/02/2020​
15/02/2020​
USA
14859​
MrGreen,NicholasSalesFTM
17/02/2020​
19/02/2020​
ENG

And the date issue has been fixed (PQ was turning dates into text).
 

Attachments

Last edited:

bretsh

New Member
Ali

Thank you so much, it has been an exciting experience for me, learning a new function and the complexities it offers.

Exactly what I needed.
 

bretsh

New Member
Evening Alan,

Thank you for sending through, will play with Power Pivot as well, always eager to learn new skills.
 
Top