Extracting and Calculating types of leave per department

[Rennier]

Good day all,

This one has me busted. I need to calculate how many days of each different leave type have been taken for each Cost Code within an Excel 2003 spreadsheet. What adds to the problem is that the data extracted from our system comes to +/- than 50 000 lines. So doing this manually is out of the question.

I have attached a spreadsheet containing an extract of the information (DATA Tab) to better understand. Under the Summary Tab I have the format in which the information must be presented. For each Cost Code/Department I need to calculate how many e.g. Annual Leave/Sick Leave/Other Leave etc. was processed for each month from January to end of October.

Has anyone dealt with something similar? I would really appreciate any assistance as this problem is way above my Excel skills (not that it’s actually above average in any case... ;-) )

Thanks in advance.
R

[Rahul Nagar]

Hello,

I think you can you pivot table for the same.

Regards
Rahul

[NBVC]

How about a Pivot Table per attached.

Note:

Added a formula in Data sheet column G to extract codes from table above:

=INDEX($C$2:$C$10,IF(ISNUMBER(MATCH(F14,$D$2:$D$10,0)),MATCH(F14,$D$2:$D$10,0),MATCH(LEFT(F14,FIND(" ",F14)-1),$D$2:$D$10,0)))

[Rennier]

How about a Pivot Table per attached.

Note:

Added a formula in Data sheet column G to extract codes from table above:

=INDEX($C$2:$C$10,IF(ISNUMBER(MATCH(F14,$D$2:$D$10,0)),MATCH(F14,$D$2:$D$10,0),MATCH(LEFT(F14,FIND(" ",F14)-1),$D$2:$D$10,0)))

excellent! although I'm a bit confused irt why there are multiples of certain months. How can we get the totals for each month per Cost Code, per Leave Type? Remember there's more than 50,000 lines of actual data...

Kind regards,
R

[NBVC]

I left out a step....

...right click the the Date captured column and select Group and Show Detail and then Group and then select Months... click Ok... all is better.

[Rennier]

Hi NBVC!

Thanks for your kind assistance. Could you suggest a quick way to get the info in the format as per the Summary tab?

Thanks again for your help!

R

[NBVC]

What's wrong with the Pivot table...

.. that is the closest and quickest way to handle that many records and separate them in to subgroups by Location3 or any other subgroup you choose.

It is also more flexible as you can add delete data with a couple of clicks.

An alternative is to use Sumproduct() formulas.. but will become extremely inefficient for that much data collection..

[Rennier]

Yep! I see what you mean. Thanks again NBVC! Have a gr8 one!

Regards,

R

[Rennier]

My rating for this assistance.

Regards,

R

[NBVC]

My rating for this assistance.

Regards,

R

Not sure what this means