Producting calender from list or vice versa

[sc247]

Morning all, new user here

A team utilises a spreadsheet laid out in a calender format

similar​

to the following

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]22/2[/TD]
[TD]23/2[/TD]
[TD]24/2[/TD]
[TD]25/2[/TD]
[TD]26/2[/TD]
[TD]27/2[/TD]
[TD]28/2[/TD]
[TD]1/3[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Job 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Job 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]Job1 & 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Job 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


But my job would be 1000x easier for all the information to be listed out as below as well as giving scope to add loads more information

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Job[/TD]
[TD]Date[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]22/2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]27/2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]24/2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]24/2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]28/2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The rest of the team require the information to be presented in the calendar format. Is there a way of generating the calender within excel that draws information from the list? Preferably as an automatic macro that would update the calendar as job dates get moved around...

If anything needs clarifying let me know and thanks for any help you can provide

 

[Rickmaurinus]

Hi!

You should take a look at Power Query (it's build into Excel). It's amazing feature called "Unpivot" is what you're looking for.

https://support.office.com/en-us/ar...er-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

https://medium.com/lets-excel/ms-ex...unpivot-in-power-query-is-awesome-f036145a479

Regards,
Rick

 

[sc247]

That looks brilliant Rick, many thanks for coming back to me! Will run a small test and if everything goes well I'll start the exciting data entry process

 

[sc247]

Sorry, is it possible to go the other way - from a list to the more aesthetically pleasing calendar?

The closest I've got so far is to use a pivot table but I either have rows for every Job (go into hundreds for each customer) or I have the row for each customer but where it should say the Job number in the calendar it just shows the total number of jobs.