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