Reassemble cells to new layout

Hi everyone,

I have an existing excel workbook with raw data, which I need to convert into another layout.

In the attached template you see the raw data. It shows all periods in which a staff member is off work.

This raw data I want to format the following way:

Instead of having every leave on a separate row I would like to have every day of 2016 as the top row (basically the x-axis) and the names (in total about 30 names) on the y-axis. And then have an x or a color formatting in for those cells where a staff member is off work.
One difficulty is also, to convert a period like in row 24 to that new format.

How can I best convert this raw data to the desired format?

In advance, thousand thanks for your help!

EDIT:

I have also added a file which shows how the final layout should look like.

Hi,

I presume the intention is to be able to easily see the analysis of absence. In which case Your suggested layout is not the most obvious way of doing it and makes any analysis much harder.

The simplest and most efficient way is with a Pivot Table which I've attached. Also note the way you recorded dates is non standard so I've converted them all to a proper date number. I've also added a couple of Slicers which allows you to easily filter the PT by name or the number of days absence. Just select as many of the items in the slicers that you want to see

....but it's a pivot table.
Just drag and drop the field names to a different position.

That's the beauty of them. In a second you can analyse stuff every which way. See changed version attached.

Originally Posted by etaver87

@BlindALley: This looks interesting. Would it be possible to convert the raw data into that template in any way? The thing is, we have a system and can draw a report like I uploaded in the raw data. So we would have to be able to feed your template with our raw data every two weeks or so.

@Richard: That looks really good too. The only thing problematic are the periods of 24 or 5 days for example. Is it possible to easily convert these numbers so that we would have only 1's?

Thank you so much for your help. Really appreciated!

Hi,

Not quite sure what you mean. Those numbers come from column F on Sheet1 and represent the number of days between the End & Start dates. There are lots of numbers of days from 1 to 16 & 19, 23 & 26. I can't actually see any 24 day period. The numbers in the PT grid are the numbers from Sheet1. If for some reason you want to show different numbers then you'll need to change column F, but I must admit I don't see why you would.

I see this thread has been spammed and for some reason I can't delete them at the moment.