📣 Is this even possible?!! Convert one schedule to another

Hello Everyone!

I am a stage manager for a live entertainment production. I have implemented a new scheduling system called Virtual Callboard and am hitting a few obstacles with trying to convert the old system to the new.

When I create a draft schedule to send to managers to review before publishing it to the entire company, the managers have a current spreadsheet format that they prefer. Although, Virtual Callboard does export the draft schedule to a csv, the managers are not thrilled with the layout and would like it to look more similar to what they are used to.

Is there anyway to create a macro (or anything else) that would convert the Schedule Draft Export to the Schedule Target Layout? I've attached both examples. The schedule data is not the same in both but it give you an idea of the layout.

I was wondering if it's possible to separate the schedule into sections according to date, title each section with the corresponding date, and clean up the date/time format.

Any help/suggestions/comments would be immensely appreciated!

Thanks... David

crossposted: https://www.mrexcel.com/board/thread...other.1224220/

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

However, if you continue to crosspost, you can expect to have your thread BLOCKED until you update it yourself.

I have a solution that appears to be close to what you are looking for.
1. In Power Query I transformed the data to allow for a pivot table presentation.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Start Date & Time", type text}}, "en-US"), "Start Date & Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Start Date & Time.1", "Start Date & Time.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Delimiter", {{"End Date & Time", type text}}, "en-US"), "End Date & Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"End Date & Time.1", "End Date & Time.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Call Name", type text}, {"Start Date & Time.1", type date}, {"Start Date & Time.2", type time}, {"End Date & Time.1", type date}, {"End Date & Time.2", type time}, {"Notes", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Start Date & Time.1", "Start Date"}, {"Start Date & Time.2", "Start Time"}, {"End Date & Time.1", "End Date"}, {"End Date & Time.2", "End Time"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Start Date"}, {{"Data", each _, type table [Call Name=nullable text, Start Date=nullable date, Start Time=nullable time, End Date=nullable date, End Time=nullable time, Notes=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Call Name", "Start Time", "End Date", "End Time", "Notes", "Index"}, {"Call Name", "Start Time", "End Date", "End Time", "Notes", "Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"End Date"})
in
    #"Removed Columns1"

I then took that transformed data and created a pivot table which included a slicer for the dates.

See the attached for both presentations.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.