Pull unique names refrencing date field

StephenM123

New Member
Joined
Apr 19, 2019
Messages
8
Afternoon all,

I'm looking for a bit of help on the following.
I've had a search through the forums but haven't been able to find what I'm after so I'm maybe looking for the wrong thing altogether.

I would like to create some sort of custom calendar, using only formula from a list of data, extract provided below.
The list currently extends some 1000 rows and will continue to expand.

Employee 1OutActiveProductionDaysPogs
Employee 2OutActiveProductionNightsTazo
Employee 3OutActiveProductionDaysPogs
Employee 3OutActiveProductionDaysPogs
Employee 4OutActiveProductionDaysPogs
Employee 5
OutActiveProductionNightsPogs
Employee 6
OutActiveProductionDaysPogs
Employee 7OutActiveProductionDaysTazo
Employee 7OutActiveProductionDaysTazo
Employee 7OutActiveProductionDaysTazo
Employee 8OutActiveProductionDaysTazo
Employee 9OutActiveProductionNightsTazo
Employee 10OutActiveProductionNightsTazo

<colgroup><col style="mso-width-source:userset;mso-width-alt:6400;width:131pt" width="175"> <col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:1572;width:32pt" width="43"> <col style="mso-width-source:userset;mso-width-alt:4352;width:89pt" width="119"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="width: 175"]Employee Name[/TD]
[TD="width: 124"]Department Name[/TD]
[TD="width: 96"]Status[/TD]
[TD="class: xl67, width: 75"]Date[/TD]
[TD="width: 43"]Hours[/TD]
[TD="width: 119"]Process[/TD]
[TD="width: 64"]Day/Night[/TD]
[TD="width: 64"]Business[/TD]
[TD="width: 75"]Week[/TD]
[TD="width: 64"]Year[/TD]

[TD="class: xl67, align: right"]10/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]11/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]12/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]13/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]10/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]11/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]10/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]11/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]12/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]13/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]17/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]10/03/2019[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

[TD="class: xl67, align: right"]10/03/2019[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]2019[/TD]

</tbody>


What I would ideally like to be able to do is pull a list of which employee is off on each day in to something similar to the format below.
I'm really struggling with how I pick up the next 'employee name' value in the lists.

[TABLE="width: 405"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]10/03/2019[/TD]
[TD][/TD]
[TD]11/03/2019[/TD]
[TD][/TD]
[TD]12/03/2019[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD][/TD]
[TD]Employee 2[/TD]
[TD][/TD]
[TD]Employee 3[/TD]
[/TR]
[TR]
[TD]Employee 4[/TD]
[TD][/TD]
[TD]Employee 5[/TD]
[TD][/TD]
[TD]Employee 7[/TD]
[/TR]
[TR]
[TD]Employee 6[/TD]
[TD][/TD]
[TD]Employee 7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Ideally after this I would like to split it out by if they work day shift or night shift as well but I should hopefully be able to do that once I get started.

Any helps on what I'm looking for would be greatly appreciated.

Thank you!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi & welcome to MrExcel.
How about


Excel 2013/2016
ABCDEFGHIJKLMNO
1Employee NameDepartment NameStatusDateHoursProcessDay/NightBusinessWeekYear10/03/201911/03/201912/03/2019
2Employee 1OutActive10/03/201910ProductionDaysPogs112019Employee 1Employee 2Employee 3
3Employee 2OutActive11/03/201910ProductionNightsTazo112019Employee 4Employee 5Employee 7
4Employee 3OutActive12/03/201910ProductionDaysPogs112019Employee 6Employee 7
5Employee 3OutActive13/03/201910ProductionDaysPogs112019Employee 9
6Employee 4OutActive10/03/201910ProductionDaysPogs112019Employee 10
7Employee 5OutActive11/03/201910ProductionNightsPogs112019
8Employee 6OutActive10/03/201910ProductionDaysPogs112019
9Employee 7OutActive11/03/201910ProductionDaysTazo112019
10Employee 7OutActive12/03/201910ProductionDaysTazo112019
11Employee 7OutActive13/03/201910ProductionDaysTazo112019
12Employee 8OutActive17/03/201910ProductionDaysTazo122019
13Employee 9OutActive10/03/20193ProductionNightsTazo112019
14Employee 10OutActive10/03/201910ProductionNightsTazo112019
Master
Cell Formulas
RangeFormula
M2=IFERROR(INDEX($A$2:$A$14,AGGREGATE(15,6,(ROW($A$2:$A$14)-ROW($A$2)+1)/(($D$2:$D$14=M$1)*($B$2:$B$14="Out")),ROWS($A$1:$A1))),"")
 
Upvote 0
This worked a treat. Thank you for the help.

My next step I plan on is looking it up and splitting it in to 2 sections one for days and one for nights.
 
Upvote 0
You're welcome & thanks for the feedback.
If you have problems with the next step, just post back
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top