HR Data, Multiple Years by Month in columns

Good afternoon Everyone,

I'm fairly experienced with excel but I'm struggling to find a solution to my problem and would appreciate any help/advice.

I have massive amounts of HR data split in to multiple worksheets by fiscal year. My column headers are the employee number, the account string charged, followed by the 12 months of our March to February fiscal year as text.

Unfortunately each employee often has multiple lines per year, as they may be charged to multiple account strings.

What I'm trying to do is combine all this data into something useful that I'll be able to summarize by employee number and a date range, while retaining the account combinations. It would be useful to be able to summarize by account combination and employee number, for specific date ranges as well.

Unfortunately I can't for the life of me think of an efficient way to organize this data towards that result. My first thought was to identify the calendar year and month for the month headings and combine all the fiscal years on one spreadsheet. I would then import this into access and use it to summarize the data. This didn't work due to matching up the data by employee number, account code and month/year. It simply takes too long and crashes excel with the lookups even when using sorted data and doing one year at a time with approximate match.

My second attempt was to combine the data on one worksheet, keeping the headings as they are, but adding an additional column for fiscal year.. this is where I am now stalled.

Using access to accomplish this has also defeated me. It can summarize by employee number, but if I want to keep the employee number and the account string, as well as separating salary and benefit lines, it doesn't work with the current data setup as it seems to give me multiple lines of the same totals and throws off the figures.

Any advice on getting this data into a usable format would be appreciated. I won't provide a large sample due to confidentiality concerns, but I've included a brief mock up of the table headings below:

Employee #	Account String	March	April	May	June	July	August	September	October	November	December	January	February	Fiscal Year
00001	12345-1234-1234-12345	5000	4000	5000	5000	5000	5000	5000	5000	5000	3500	5000	5000	2014-15
00001	12345-1234-1234-99999	5000	4000	5000	5000	5000	5000	5000	5000	5000	3500	5000	5000	2015-16
00001	12345-1234-1234-77777	250	350	500	500	500	500	500	500	500	350	500	500	2014-15

Thanks in advance!

I'd restructure your data like attached (see sheet2).

This can be done via PowerQuery or VBA. Since you have Excel 2010, I'd suggest using PowerQuery.

For PowerQuery.
- First convert data to Excel Table
- Load Table to PowerQuery
- In the editor move Fiscal Year column to 3rd position
- Select all "Month" columns and right click -> Unpivot columns
- Rename Attribute column to "Month"

From there, you can use PivotTable to summarize data by Employee# and/or Account string.

Note: You may also want to add date column (i.e. 1st date of month including year). This allows use of timeline slicer and other time intelligence.

Ah, I got you.

You don't need to load the result back onto sheet. You can just load as connection only, while checking load to data model option.
You can then use the data model as data source for pivot table to report on it.

That way, you won't run into row limit issue.

Here's sample pivot table loaded directly from data model.