Index Match :(

[Kamran01]

Dear Experts,

Please have a look on below chart, this is showing date wise in and out.

[TABLE="width: 846"]
<colgroup><col><col><col><col><col><col span="10"></colgroup><tbody>[TR]
[TD]S.No.[/TD]
[TD]Employee[/TD]
[TD]Name[/TD]
[TD]Father/Name[/TD]
[TD]Designation[/TD]
[TD]01[/TD]
[TD]01[/TD]
[TD]02[/TD]
[TD]02[/TD]
[TD]03[/TD]
[TD]03[/TD]
[TD]04[/TD]
[TD]04[/TD]
[TD]05[/TD]
[TD]05[/TD]
[/TR]
[TR]
[TD]In[/TD]
[TD]Out[/TD]
[TD]In[/TD]
[TD]Out[/TD]
[TD]In[/TD]
[TD]Out[/TD]
[TD]In[/TD]
[TD]Out[/TD]
[TD]In[/TD]
[TD]Out[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]187430[/TD]
[TD]Andress Mashi [/TD]
[TD]Iqbal Mashi[/TD]
[TD]Sweeper[/TD]
[TD]8:05[/TD]
[TD]20:15[/TD]
[TD="colspan: 2"]Sunday[/TD]
[TD]8:18[/TD]
[TD]19:01[/TD]
[TD]8:05[/TD]
[TD]19:08[/TD]
[TD]8:11[/TD]
[TD]19:19[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]170602[/TD]
[TD]Muhammad Ismail [/TD]
[TD]Sawan[/TD]
[TD]Sweeper[/TD]
[TD]8:07[/TD]
[TD]20:23[/TD]
[TD="colspan: 2"]Sunday[/TD]
[TD]8:07[/TD]
[TD]20:23[/TD]
[TD]8:07[/TD]
[TD]20:23[/TD]
[TD]8:07[/TD]
[TD]20:23[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]189363[/TD]
[TD]Abdul Khaliq [/TD]
[TD]Laal Buksh[/TD]
[TD]Sweeper[/TD]
[TD]8:05[/TD]
[TD]20:15[/TD]
[TD="colspan: 2"]Sunday[/TD]
[TD]8:05[/TD]
[TD]20:15[/TD]
[TD]8:05[/TD]
[TD]20:15[/TD]
[TD]8:05[/TD]
[TD]20:15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]154396[/TD]
[TD]Muhammad Zahid [/TD]
[TD]Abdul Hameed[/TD]
[TD]Sweeper[/TD]
[TD]8:05[/TD]
[TD]20:15[/TD]
[TD="colspan: 2"]Sunday[/TD]
[TD]8:18[/TD]
[TD]19:01[/TD]
[TD]8:05[/TD]
[TD]19:08[/TD]
[TD]8:11[/TD]
[TD]19:19[/TD]
[/TR]
</tbody>[/TABLE]

I want result accordingly, how i can use index match function easily.

[TABLE="width: 694"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]EBS #[/TD]
[TD]EMP. NAME[/TD]
[TD]FATHER NAME[/TD]
[TD]DESIGNATION[/TD]
[TD]DATE[/TD]
[TD]IN[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]187430[/TD]
[TD]Andress Mashi [/TD]
[TD]Iqbal Mashi[/TD]
[TD]Sweeper[/TD]
[TD]01-Sep-18[/TD]
[TD]8:05 AM[/TD]
[TD]8:15 PM[/TD]
[/TR]
[TR]
[TD]187430[/TD]
[TD]Andress Mashi [/TD]
[TD]Iqbal Mashi[/TD]
[TD]Sweeper[/TD]
[TD]02-Sep-18[/TD]
[TD]Holiday[/TD]
[TD]Holiday[/TD]
[/TR]
[TR]
[TD]187430[/TD]
[TD]Andress Mashi [/TD]
[TD]Iqbal Mashi[/TD]
[TD]Sweeper[/TD]
[TD]03-Sep-18[/TD]
[TD]8:18 AM[/TD]
[TD]7:01 PM[/TD]
[/TR]
[TR]
[TD]187430[/TD]
[TD]Andress Mashi [/TD]
[TD]Iqbal Mashi[/TD]
[TD]Sweeper[/TD]
[TD]04-Sep-18[/TD]
[TD]8:05 AM[/TD]
[TD]7:08 PM[/TD]
[/TR]
[TR]
[TD]187430[/TD]
[TD]Andress Mashi [/TD]
[TD]Iqbal Mashi[/TD]
[TD]Sweeper[/TD]
[TD]05-Sep-18[/TD]
[TD]8:11 AM[/TD]
[TD]7:19 PM[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
Best Regard,
Kamran noor

 

[steve the fish]

Heres a few to try.

In A11:

=INT((ROW(A1)-1)/5)+1

In B11:

=INDEX(B$3:B$6,$A11)

copy across to E11.

In F11:

=INDEX($F$3:$O$6,$A11,MOD(ROWS($A$1:A1)*2-1,10))

In G11:

=INDEX($F$3:$O$6,$A11,1+MOD(ROWS($A$1:A1)*2-1,10))

Drag them all down until you get errors.