Need to get reports from badly formatted exported data

[TryingToLearnUK]

Hi everyone second post, so hope I get this right!

Attached is an example of the excel sheet a system gives when you select 'export to excel'. It (badly!) shows the employee name, leave type, leave dates and durations.

What I need to be able to do is either create a tab that has formulas to pull the key info, or a pivot table. I have posted here rather than the pivot forum as the current format won't allow a pivot to be created (so guessed I would need to reformat with formulas before I can create a pivot).

The information I need to summarise from this data is:
- How many days leave in a set time period for each leave type (there are 15 types in total and days are listed in col C)?
- Based on the above, which employees had that type of leave?

Examples of queries I need to answer based on the summarised data:
- How many people were sick in September?
- How long was the average person sick in September?
- How many days were taken as holiday in September?
- Who was on mat leave in September?


I really hope I have explained clearly, thank you in advance.

[Bernie Deitrick]

You can normalize your data set into a usable table by using columns of formulas to extract the information into one row for each of the 5 row blocks that you have. In the attached, I used formula in H4:M4, and I copied the yellow cells (5 rows by 6 columns) into the green cells (20 rows by 6 columns). The usefulness of this technique can be extended to blocks of data that vary in row size; that requires using a lot of ifs and having formulas in every row instead of just one out of five.

Then you can use a pivot table or other summary on those 6 columns.

[TryingToLearnUK]

Hi Bernie, thank you so much for responding. This approach works great, until I found there were some individuals with several leave instances, or where the exported data spits out a new location name so the order goes awry.

I have attached a doc with 2 new tabs, 1 shows the instance where the 2+ leave occurrences mean your approach won't work, and 1 shows another way we can get the data (it exports to Word and this is a copy into excel, the actual export is 4k lines and has graphs (as I CTRL+A Word)).

I think it will need some kind of index formula? I managed a summary table to count the leave types (col A), but could not work out how to make it return all the other elements I need

Thank you!

[Bernie Deitrick]

Here is a file with the conditional formulas that fill the entire table....

[TryingToLearnUK]

Once again, thank you, I truly appreciate your support here. It is almost working! I have copied the details of an employee who had several leave instances, your formula doesn't capture these?

If it is me missing the obvious please let me know!!

[Bernie Deitrick]

I think this will work.

[TryingToLearnUK]

Wahoo I think we are there It carries on the last name forever but that isn't the end of the world! The only thing I am struggling with now, is working out how many days are in September. Can't get the period of time cut in the pivot, so looking to add a column in the data tab for number of days the period falls into each month - does that make sense?

[Bernie Deitrick]

Sure - the formulas should be easy - take a look at the EOMONTH function - or use DATE(YEAR(cell),MONTH(cell),0) to return the last day of the previous month or DATE(YEAR(cell),MONTH(cell)+1,0) to return the last day of the month, for splitting purposes. You may need to use WORKDAY as well, with a list of holidays.....

Start a new thread if you have any date math questions...

[TryingToLearnUK]

You are brilliant - thank you so so much!! I will close the thread as all resolved