module 04

paupol2004
EmployeeCensus.xlsx
Home>Computer Science homework help>module 04

Instruction Sheet

In order to compare these lists, an excel function must be used. This excel function will allow the computer to compare a set value against a vertical list of data. This function is called a VLOOKUP.
In this case we have two lists, the first is a list gathered from an LDAP query in Active Directory named AD List, the second is an employee census report gathered from XYZ Tech's HR department called HR Sheet
When reviewing the two sheets the only common field of data is the Employee ID field. This is the common element that we can search against. One special note: the common field to be referenced must be in the first column to the left. In this case the HR census report came in this order, but if the need arises, this can be done with a simple copy and paste.
The excel formula that we are going to create will pull the termination date from the HR list and place that onto the cell at the end of the AD List data in Column O. If the employee is still active, there is no data and the formula will return an error. Then we will filter this list of data to show only those employees who have been terminated to determine if any accounts remain active.
To perform this action follow the steps detailed below:
1 Highlight the Employee Census List in the HR list Sheet - Cells A2:J40
2 Click in the Name Box found at the upper left corner contains A2 in the box, and name this selection HR
3 Go to the AD List and Click on Cell O1 and enter the heading Termination Date and press enter
4 In Cell O2 enter the following formula exactly
=VLOOKUP(I2,HR,9,FALSE)
6 Click on Cell O2 and choose Copy
7 Select cells O3:O40 and choose Paste
This gives you the dates when an employee is terminated, and a blank for those that are still active
8 Click on a termination date, and then choose Sort & Filter and then Filter from the Home Tab which will create dropdown arrows at the end of all the headings.
9 Last is to click the drop down next to Termination Date and uncheck Blanks. This will give you only a list of those that are terminated.

AD List

SAMAccountName CN FirstName LastName lastLogon Last Authenticated DC pwdLastSet badPwdCount PasswordNeverExpires Employee ID logonCount Enabled Lockout Mail
Admiral.Raddus Admiral Raddus Admiral Raddus 10/28/17 pdc02.xyztech.com 10/10/16 0 FALSE 998279 12408 TRUE FALSE Admiral.Raddus@xyztech.com
Aunt.Beru Aunt Beru Aunt Beru 11/1/17 pdc02.xyztech.com 12/2/16 0 FALSE 998542 2078 TRUE FALSE Aunt.Beru@xyztech.com
Bail.Organa Bail Organa Bail Organa 11/1/17 pdc02.xyztech.com 12/19/16 0 FALSE 998466 4080 TRUE FALSE Bail.Organa@xyztech.com
Baze.Malbus Baze Malbus Baze Malbus 11/1/17 pdc02.xyztech.com 6/13/16 0 FALSE 998017 29171 TRUE FALSE Baze.Malbus@xyztech.com
Bodhi.Rock Bodhi Rock Bodhi Rock 11/1/17 pdc02.xyztech.com 5/16/16 0 FALSE 998607 5192 TRUE FALSE Bodhi.Rock@xyztech.com
Brea.Tonnika Brea Tonnika Brea Tonnika 10/13/17 pdc02.xyztech.com 6/13/16 0 FALSE 998096 16635 FALSE FALSE Brea.Tonnika@xyztech.com
Cassian.Andor Cassian Andor Cassian Andor 10/28/17 pdc02.xyztech.com 8/29/16 0 FALSE 998008 13781 TRUE FALSE Cassian.Andor@xyztech.com
Chall.Bekan Chall Bekan Chall Bekan 10/25/17 pdc02.xyztech.com 10/28/16 0 FALSE 998664 22705 TRUE FALSE Chall.Bekan@xyztech.com
Chirrut.Imwe Chirrut Imwe Chirrut Imwe 10/25/17 pdc02.xyztech.com 6/13/16 0 FALSE 998067 72461 TRUE FALSE Chirrut.Imwe@xyztech.com
Colonel.Yularen Colonel Yularen Colonel Yularen 10/29/17 pdc02.xyztech.com 7/27/16 0 FALSE 998030 71574 TRUE FALSE Colonel.Yularen@xyztech.com
Djas.Puhr Djas Puhr Djas Puhr 5/30/17 pdc02.xyztech.com 2/9/10 0 FALSE 998750 20637 FALSE FALSE Djas.Puhr@xyztech.com
Ezra.Bridger Ezra Bridger Ezra Bridger 10/2/16 pdc02.xyztech.com 8/15/16 0 FALSE 998805 570 FALSE FALSE Ezra.Bridger@xyztech.com
Gail.Trayvis Gail Trayvis Gail Trayvis 11/1/17 pdc02.xyztech.com 6/8/16 0 FALSE 998695 26410 TRUE FALSE Gail.Trayvis@xyztech.com
Galen.Erso Galen Erso Galen Erso 12/22/16 pdc02.xyztech.com 6/20/16 0 FALSE 998399 17286 FALSE FALSE Galen.Erso@xyztech.com
Gar.Saxon Gar Saxon Gar Saxon 11/1/17 pdc02.xyztech.com 12/27/16 0 FALSE 998110 1242 TRUE FALSE Gar.Saxon@xyztech.com
Hondo.Ohnaka Hondo Ohnaka Hondo Ohnaka 11/1/17 pdc02.xyztech.com 9/12/16 0 FALSE 998114 12972 TRUE FALSE Hondo.Ohnaka@xyztech.com
Hrchek.Kalfas Hrchek Kalfas Hrchek Kalfas 4/7/17 pdc02.xyztech.com 6/7/16 0 FALSE 998671 12639 FALSE FALSE Hrchek.Kalfas@xyztech.com
Jai.Kell Jai Kell Jai Kell 11/1/17 pdc02.xyztech.com 8/15/16 0 FALSE 998716 26276 TRUE FALSE Jai.Kell@xyztech.com
Jyn.Erso Jyn Erso Jyn Erso 10/2/16 pdc02.xyztech.com 8/2/16 0 FALSE 998347 15884 FALSE FALSE Jyn.Erso@xyztech.com
Leesub.Sirin Leesub Sirin Leesub Sirin 11/1/17 pdc02.xyztech.com 6/11/16 0 FALSE 998084 87086 TRUE FALSE Leesub.Sirin@xyztech.com
Luke.Skywalker Luke Skywalker Luke Skywalker 1/28/17 pdc02.xyztech.com 6/13/16 0 FALSE 998613 73267 FALSE FALSE Luke.Skywalker@xyztech.com
Lyra.Erso Lyra Erso Lyra Erso 10/25/17 pdc02.xyztech.com 12/19/16 0 FALSE 998075 43169 TRUE FALSE Lyra.Erso@xyztech.com
Maz.Kanata Maz Kanata Maz Kanata 11/1/17 pdc02.xyztech.com 1/9/17 0 FALSE 998349 6541 TRUE FALSE Maz.Kanata@xyztech.com
Mon.Mothma Mon Mothma Mon Mothma 10/29/17 pdc02.xyztech.com 5/12/16 18 FALSE 998426 24277 TRUE TRUE Mon.Mothma@xyztech.com
Nabrun.Leids Nabrun Leids Nabrun Leids 10/25/17 pdc02.xyztech.com 6/13/16 0 FALSE 998439 31009 TRUE FALSE Nabrun.Leids@xyztech.com
Nien.Numb Nien Numb Nien Numb 10/25/17 pdc02.xyztech.com 5/9/16 0 FALSE 998216 76785 TRUE FALSE Nien.Numb@xyztech.com
Poe.Damer Poe Dameron Poe Dameron 11/1/17 pdc02.xyztech.com 6/11/16 0 FALSE 998807 5104 TRUE FALSE Poe.Dameron@xyztech.com
Ponda.Baba Ponda Baba Ponda Baba 11/1/17 pdc02.xyztech.com 11/21/16 0 FALSE 998783 6889 TRUE FALSE Ponda.Baba@xyztech.com
Rose.Tico Rose Tico Rose Tico 11/1/17 pdc02.xyztech.com 6/13/16 0 FALSE 998783 30662 TRUE FALSE Rose.Tico@xyztech.com
Rycar.Ryjerd Rycar Ryjerd Rycar Ryjerd 5/18/17 pdc02.xyztech.com 9/12/16 0 FALSE 998235 2071 FALSE FALSE Rycar.Ryjerd@xyztech.com
Ryder.Azadi Ryder Azadi Ryder Azadi 11/1/17 pdc02.xyztech.com 8/10/16 0 FALSE 998165 29561 TRUE FALSE Ryder.Azadi@xyztech.com
Sabine.Wren Sabine Wren Sabine Wren 11/1/17 pdc02.xyztech.com 8/3/15 1 FALSE 998678 1803 TRUE FALSE Sabine.Wren@xyztech.com
Saw.Gerrera Saw Gerrera Saw Gerrera 11/1/17 pdc02.xyztech.com 6/12/16 0 FALSE 998320 30211 TRUE FALSE Saw.Gerrera@xyztech.com
Senni.Tonnika Senni Tonnika Senni Tonnika 10/28/16 pdc02.xyztech.com 9/29/16 0 FALSE 998009 10051 FALSE FALSE Senni.Tonnika@xyztech.com
Swilla.Corey Swilla Corey Swilla Corey 10/28/17 pdc02.xyztech.com 10/10/16 0 FALSE 998870 22129 TRUE FALSE Swilla.Corey@xyztech.com
Uncle.Owen Uncle Owen Uncle Owen 10/18/17 pdc02.xyztech.com 6/8/16 0 FALSE 998872 8801 FALSE FALSE Uncle.Owen@xyztech.com
Weeteef.Cyubee Weeteef Cyubee Weeteef Cyubee 11/1/17 pdc02.xyztech.com 6/29/16 0 FALSE 998779 33906 TRUE FALSE Weeteef.Cyubee@xyztech.com
Yogar.Lyste Yogar Lyste Yogar Lyste 11/1/17 pdc02.xyztech.com 7/15/16 0 FALSE 998179 22734 TRUE FALSE Yogar.Lyste@xyztech.com
Zare.Leonis Zare Leonis Zare Leonis 10/8/17 pdc02.xyztech.com 3/11/16 0 FALSE 998206 1313 TRUE FALSE Zare.Leonis@xyztech.com

HR Sheet

Employee ID Employee Name Department ID Location Desc full Employee Status Full/Part Reg/Temp Rehire Date Term Date Company Hire Date
998008 Andor, Cassian 9EOE7086 XYZ Tech LLC-HQ Active Full-Time Regular 12/13/2012
998165 Azadi, Ryder 9EOE7057 Fairbanks Active Full-Time Regular 11/13/2012
998783 Baba, Ponda 9EOE7058 Fairbanks Whearhouse Active Full-Time Regular 09/30/2010
998664 Bekan, Chall 9EOE7062 Fairbanks Active Full-Time Regular 10/04/2004
998542 Beru, Aunt 9EOE7082 XYZ Tech LLC-HQ Active Full-Time Regular 12/15/2008
998805 Bridger, Ezra 9EOE7057 Fairbanks Terminated Full-Time Regular 10/2/17 01/01/2014
998870 Corey, Swilla 9EOE7081 Remote TX Active Full-Time Regular 07/22/2013
998779 Cyubee, Weeteef 9EOE7057 Fairbanks Active Full-Time Regular 07/07/2008
998807 Dameron, Poe 9EOE7012 Remote OH Active Full-Time Regular 05/18/2015
998399 Erso, Galen 9EOE7012 Remote CA Terminated Full-Time Regular 12/22/16 07/09/2014
998347 Erso, Jyn 9EOE7083 XYZ Tech LLC-HQ Terminated Full-Time Regular 10/2/16 01/01/2014
998075 Erso, Lyra 9EOE7081 Florida Active Full-Time Regular 07/09/2009
998320 Gerrera, Saw 9EOE7062 Remote MD Active Full-Time Regular 01/13/2014
998067 Imwe, Chirrut 9EOE7062 Florida Active Full-Time Regular 01/15/2007
998671 Kalfas, Hrchek 9EOE7082 XYZ Tech LLC-HQ Terminated Full-Time Regular 4/7/18 07/26/2010
998349 Kanata, Maz 9EOE7081 XYZ Tech LLC-HQ Active Full-Time Regular 01/07/2013
998716 Kell, Jai 9EOE7057 Fairbanks Active Full-Time Regular 09/21/2009
998439 Leids, Nabrun 9EOE7069 Remote GA Active Full-Time Regular 04/04/2012
998206 Leonis, Zare 9EOE7057 Fairbanks Terminated Full-Time Regular 10/8/17 04/21/2014
998179 Lyste, Yogar 9EOE7012 Remote FL Active Full-Time Regular 02/10/2014
998017 Malbus, Baze 9EOE7069 XYZ Tech LLC-HQ Active Full-Time Regular 06/01/2010
998426 Mothma, Mon 9EOE7024 Fairbanks Active Full-Time Regular 03/17/2014
998216 Numb, Nien 9EOE7058 Fairbanks Whearhouse Active Full-Time Regular 05/19/2014
998114 Ohnaka, Hondo 9EOE7057 Fairbanks Active Full-Time Regular 07/07/2014
998466 Organa, Bail 9EOE7057 Fairbanks Active Full-Time Regular 01/06/2012
998872 Owen, Uncle 9EOE7082 XYZ Tech LLC-HQ Terminated Full-Time Regular 10/18/17 10/29/2012
998750 Puhr, Djas 9EOE7024 XYZ Tech LLC-HQ Terminated Full-Time Regular 5/31/17 04/06/2015
998279 Raddus, Admiral 9EOE7057 Remote VA Active Full-Time Regular 10/22/2012
998607 Rock, Bodhi 9EOE7069 Remote CA Active Full-Time Regular 10/30/1989
998235 Ryjerd, Rycar 9EOE6085 XYZ Tech LLC-HQ Terminated Full-Time Regular 5/20/17 07/30/2015
998110 Saxon, Gar 9EOE7012 Fairbanks Active Full-Time Regular 05/09/2005
998084 Sirin, Leesub 9EOE7057 Fairbanks Active Full-Time Regular 04/06/2015
998613 Skywalker, Luke 9EOE7057 Fairbanks Terminated Full-Time Regular 1/29/17 10/01/2015
998055 Tico, Rose 9EOE7062 Remote WI Active Full-Time Regular 05/03/2010
998096 Tonnika, Brea 9EOE7058 Fairbanks Whearhouse Terminated Part-Time Temporary 5/13/17 07/03/2015
998009 Tonnika, Senni 9EOE6085 XYZ Tech LLC-HQ Terminated Full-Time Regular 11/1/16 04/13/2015
998695 Trayvis, Gail 9EOE7055 XYZ Tech LLC-HQ Terminated Full-Time Regular 10/30/17 10/01/2012
998678 Wren Sabine 9EOE7082 XYZ Tech LLC-HQ Active Full-Time Regular 01/03/2013
998030 Yularen, Colonel 9EOE7082 Remote FL Active Full-Time Regular 02/22/2010