• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidate Report

N

Nandakumar

Member
Dear Friends,

I required your help on the below:

My data sheet is as below:
EMP ID1-Jan2-Jan3-Jan4-Jan5-Jan6-Jan
1001PresentAbsentPresentAbsentAbsentAbsent
1002AbsentPresentPresentPresentPresentPresent

I want report for absenteeism as below:

EMP IDFromTo
10012-Jan2-Jan
10014-Jan6-Jan
10021-Jan1-Jan

Thanks in advance
 
Dear Naranyan,

Please find attached upload sheet for your kind help.

Regards
Nandakumar
 

Attachments

  • Upload.xlsx
    9 KB · Views: 2
Dear Narayan,

Somewhat formula is not working in my data sheet.
Attaching my data sheet.A stands for Absent and P stands for present.
I want report only for A ie Absent
Please help!


Regards
Nandakumar
 

Attachments

  • Upload2.xlsx
    143.6 KB · Views: 1
Hi Nandakumar ,

Sorry , but the first uploaded file had a 0 entry at the end , which is why the problem did not manifest itself. Your actual working file had a lot of 0 absences , and so another intermediate step was required to eliminate these zero entries.

I have used a pivot table ; as and when you modify your data , or in any other way update it , you need to refresh your pivot table.

Secondly , the range is at present a static range ; once you confirm that everything works correctly , we can introduce a dynamic named range so that the formulae themselves will not need to be changed.

Since the file size now exceeds 1 MB , I have uploaded it to my DropBox account ; you can download it using the following link :

https://www.dropbox.com/s/lox6qzj9cjnr9fg/Nandakumar_Upload2.xlsx?dl=0

Narayan
 
You must log in or register to reply here.
Back
Top