• 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.

Help Needed-Combining Multiple Rows of Data into Single Row & Identifying Scenarios and inputing remarks

starmumbai

New Member
Dear All,
I would request your help in providing solution to the below problem.

Scenario:
I have approx 1500 employees in my organisation who need to compply for a block leave which should be continous 5 days.Employees apply through a leave system and I am able to pull a report of all the block leaves and sort it by employee id's.However there is no system hardstop preventing the employee from marking block leaves which are a) non continous & b) less than 5 days.

Scenario 1:Could you please assist in setting up some formula which will help me identify employees who have taken
a) non continous & b) less than 5 days.
Scenario 2:The final output should combine multiple rows of data into one row as shown in final output.
a)Calculate (add) the total number of leaves against each employee
b)Inputs the Earliest start date and latest end dates comparing multiple rows.
c)Highlight the non continous & less than 5 days and input comments as shown in sample.

Thankyou
 

Attachments

  • Leaves.xlsx
    12.2 KB · Views: 2
Try,

1] L6 (RowLabels) copied down :

=IFERROR(SMALL($D$6:$D$26,COUNTIF($D$6:$D$26,"<="&L5)+1),"")

2] M6 (Name) copied across to P6 and all copied down :

=IF($L6="","",VLOOKUP($L6,$D$6:$I$26,MATCH(M$5,$D$5:$I$5,0),0+(M$5="End Date")))

3] Q6 (Duration) copied down :

=IF($L6="","",SUMIF($D:$D,$L6,$I:$I))

4] R6 (Remark) copied down :

=IF(L6="","",IF(Q6<5,"Less than 5 days",IF(Q6>=NETWORKDAYS(O6,P6),"OK","Non Continous")))

70031
 

Attachments

  • Leaves (BY).xlsx
    16.7 KB · Views: 4
Thanks Bosco for your prompt help!!
I will keep you posted once i am able to test these formulas in the actual data.
 
Back
Top