module 04

paupol2004
CIS3240C_Mod4_Part_2.xlsx.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 Termination Date
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
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
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
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 12/22/16
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
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
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
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 10/2/17
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 4/7/18
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
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
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
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 10/18/17
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 5/20/17
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
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 1/29/17
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 10/8/17
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
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
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
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
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
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
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
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
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
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
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
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
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 10/2/16
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 10/30/17
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
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
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 5/31/17
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 5/13/17
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 11/1/16

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

Comparison

SAMAccountName CN FirstName LastName lastLogon Last Authenticated DC pwdLastSet badPwdCount PasswordNeverExpires Employee ID logonCount Enabled Lockout Mail Termination Date Employee Status
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 Active
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 Active
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 Active
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 12/22/16 Terminated
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 Active
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 Active
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 Active
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 10/2/17 Terminated
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 4/7/18 Terminated
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 Active
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 Active
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 Active
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 Terminated
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 10/18/17 Terminated
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 5/20/17 Terminated
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 Active
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 1/29/17 Terminated
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 10/8/17 Terminated
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 Active
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 Active
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 Active
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 Active
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 Active
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 Active
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 Active
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 Active
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 Active
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 Active
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 Active
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 Terminated
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 Active
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 10/2/16 Terminated
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 10/30/17 Terminated
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 Active
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 Active
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 Active
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 5/31/17 Terminated
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 5/13/17 Terminated
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 11/1/16 Terminated