Making a daily report from employees schedule.

[Tasslehoff]

Hello there!

A couple of months not touching Excel seem to have dried my brain out and I'm out of ideas.
I have this type of schedule table for around 60 ppl that extends throughout the year.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]01/01/18[/TD]
[TD]02/01/18[/TD]
[TD]03/01/18[/TD]
[TD]04/01/18[/TD]
[/TR]
[TR]
[TD]John [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Ringo[/TD]
[TD]B1[/TD]
[TD]A1[/TD]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Luciano[/TD]
[TD]Training[/TD]
[TD]B[/TD]
[TD]B1[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]Jose[/TD]
[TD]A[/TD]
[TD]OFF[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Placido[/TD]
[TD]OFF[/TD]
[TD]A[/TD]
[TD]A1[/TD]
[TD]A1[/TD]
[/TR]
</tbody>[/TABLE]

I need to print out a report that looks like:

[TABLE="width: 139"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]01/01/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shift A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jose[/TD]
[/TR]
[TR]
[TD]Shift B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]George[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shift A1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]Shift B1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ringo[/TD]
[/TR]
</tbody>[/TABLE]

I know there must be a way with formulas to do this, but I am stuck right now...

thanks in advance for your help!

 

[mse330]

Hi Tasslehoff

I think a pivot table would be your best way to achieve the required output without any VBA coding but you need to change the dataset structure to be able to use it in the pivot table

Just follow the below steps carefully:

• Add header in column A above the name as "Names"
• Press Alt+D+P in your keyboard then a PivotTable Wizard form will appear
• Click on Multiple Consolidation Ranges then click Next
• In step 2, click on I will create the page fields then Next
• In step 3, select the range of all your data then click Finish
• Now excel should have created a new sheet with a pivot table of your info but this is not the desired look
• From pivot table fields, de-select all items expect what's under the values
• Now the pivot table should only show 1 total, double-click it
• Excel should now create a new sheet with a table having the same data of your original data but in a different format
• Rename the table headings so it would make sense for you in the next step
• You can now make a pivot table of this new table & format it in a way to show the report in the desired format

Let me know if that works for you or you need any further help

 

[Tasslehoff]

I will test this tomorrow at work thanks!!