# 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

• 12.2 KB Views: 2

#### bosco_yip

##### Excel Ninja
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")))

#### Attachments

• 16.7 KB Views: 4

#### starmumbai

##### New Member
Thanks Bosco for your prompt help!!
I will keep you posted once i am able to test these formulas in the actual data.