We need to take the waitlist sign-up data and combine it with information we’ve collected from the next step: registration. Merging the datasets is going to allow us to source some quick insights that we wouldn’t be able to find if we tried to analyze each data pull individually. Additionally, we want to calculate some new values based on the collected data. Again, the idea here is we want to understand our customer as best as we can. With more information we can better serve them by adjusting our product and/or messaging to fit their needs.
The goal is to have a single spreadsheet that combines the two different tables and includes every field available. We're always going to lose folks between signing up for the waitlist and actually registering, so I only want to see data for those that have registered (delete those that didn’t register). Then I need you to add-in the following fields for each record as new calculated columns:
1. ‘registration_days’ = how many days between when the individual received the registration link and when they completed the registration process
2. ‘age_at_registration' = how old (whole years) the individual was when they completed the registration process
Here are some resources that should get you started:
1. Basics of VLOOKUP and HLOOKUP – Leila Gharani(youtube videos)
2. Use VLOOKUP to merge tables - Exceljet(youtube videos)
3. Basic Excel formulas and functions – Kevin Stratvert(youtube videos)
fname,lname,email,school,year,source Bird,Agdahl,
[email protected],Rosslyn Polytechnic University,College Senior,Handshake Brietta,Dewitt,
[email protected],Clarendon State University,College Senior,Handshake Jamison,Harring,
[email protected],Rosslyn Polytechnic University,College Junior,Handshake Michaella,Sarvar,
[email protected],University of Arlington,College Sophomore,Handshake Emmalynn,Drover,
[email protected],University of Arlington,College Senior,Handshake Rosabel,Beckingham,
[email protected],University of Arlington,College Senior,Handshake Therine,Cheng,
[email protected],Rosslyn Polytechnic University,College Sophomore,Referral Lyndsie,Ferroni,
[email protected],Clarendon State University,College Senior,Handshake Lonnard,De Witt,
[email protected],University of Arlington,College Junior,Handshake Agace,Jebb,
[email protected],Rosslyn Polytechnic University,College Senior,Handshake Daryn,Cash,
[email protected],University of Arlington,College Junior,Social Media Dru,Ramelot,
[email protected],Pentagon City Community College,College Freshman,Handshake Daphene,Crock,
[email protected],University of Arlington,College Freshman,Handshake Wood,Paynter,
[email protected],Crystal City Community College,College Sophomore,Handshake Margo,Fadian,
[email protected],Pentagon City Community College,College Sophomore,Handshake Lyn,Shermore,
[email protected],Ballston College,College Senior,Handshake Thomasina,Pietrowicz,
[email protected],Rosslyn Polytechnic University,College Sophomore,Handshake Rutter,Huntly,
[email protected],Pentagon City Community College,College Sophomore,Handshake Ronnica,Calow,
[email protected],Ballston College,College Sophomore,Handshake Norby,Pattillo,
[email protected],Rosslyn Polytechnic University,College Junior,Handshake Umberto,Maton,
[email protected],Crystal City Community College,College Sophomore,Handshake Minni,Gilbey,
[email protected],Rosslyn Polytechnic University,College Junior,Handshake Luis,Aldrin,
[email protected],Clarendon State University,College Freshman,Handshake Jacquelyn,Sandom,
[email protected],Clarendon State University,College Senior,Handshake Cissiee,Tapner,
[email protected],Rosslyn Polytechnic University,College Senior,Handshake Noella,Whitehead,
[email protected],Ballston College,College Senior,Handshake Britt,Folley,
[email protected],Ballston College,College Freshman,Handshake Huey,Couroy,
[email protected],Crystal City Community College,College Sophomore,Other Ephraim,Feetham,
[email protected],University of Arlington,College Junior,Handshake Bink,Stallan,
[email protected],Crystal City Community College,College Freshman,Handshake Dorthea,Eade,
[email protected],Clarendon State University,College Junior,Handshake Willi,Haw,
[email protected],University of Arlington,College Sophomore,Handshake Rica,Bernardo,
[email protected],Crystal City Community College,College Freshman,Handshake Elianore,Chasmer,
[email protected],Ballston College,College Sophomore,Handshake Raymond,Tulleth,
[email protected],Clarendon State University,College Freshman,Handshake Britteny,Gloster,
[email protected],University of Arlington,College Senior,Social Media Hamnet,Plowright,
[email protected],Ballston College,College Sophomore,Handshake Jorge,Cuzen,
[email protected],Ballston College,College Freshman,Handshake Norri,McNeachtain,
[email protected],Rosslyn Polytechnic University,College Senior,Handshake Charissa,Hoggetts,
[email protected],University of Arlington,College Senior,Handshake Tobit,Brittoner,
[email protected],University of Arlington,College Junior,Handshake Sadella,Tytcomb,
[email protected],Ballston College,College Junior,Handshake Vernice,Mc Gee,
[email protected],Ballston College,College Senior,Handshake Kittie,Beig,
[email protected],Ballston College,College Sophomore,Handshake Errick,Elman,
[email protected],Pentagon City Community College,College Freshman,Handshake Shaughn,Kemery,
[email protected],University of Arlington,College Sophomore,Handshake Antons,Margetson,
[email protected],Clarendon State University,College Freshman,Handshake Raffarty,Foye,
[email protected],Crystal City Community College,College Sophomore,Handshake Farley,Dolphin,
[email protected],Pentagon City Community College,College Sophomore,Handshake Claretta,Hanton,
[email protected],University of Arlington,College Freshman,Handshake Babb,Askam,
[email protected],Rosslyn Polytechnic University,College Senior,Social Media Jennee,Wretham,
[email protected],Clarendon State University,College Senior,Handshake Lewie,Nund,
[email protected],Crystal City Community College,College Freshman,Handshake Algernon,Kiessel,
[email protected],University of Arlington,College Sophomore,Handshake Fidel,Howley,
[email protected],Ballston College,College Freshman,Handshake Torrence,Kief,
[email protected],Clarendon State University,College Junior,Handshake Wendall,Mableson,
[email protected],University of Arlington,College Senior,Handshake Vanda,Caldero,
[email protected],University of Arlington,College Senior,Handshake Roxy,Sumpton,
[email protected],Clarendon State University,College Senior,Handshake Haley,Duckhouse,
[email protected],Pentagon City Community College,College Freshman,Handshake Tandi,Spadeck,
[email protected],Rosslyn Polytechnic University,College Freshman,Handshake Corney,Gorry,
[email protected],University of Arlington,College Freshman,Handshake Audre,Lockhead,
[email protected],University of Arlington,College Junior,Handshake Devland,Robbs,
[email protected],Pentagon City Community College,College Sophomore,Handshake Daloris,Matus,
[email protected],Crystal City Community College,College Sophomore,Handshake Abelard,Autie,
[email protected],Ballston College,College Senior,Social Media Abby,Stevani,
[email protected],Pentagon City Community College,College Sophomore,Social Media Bill,Odeson,
[email protected],Rosslyn Polytechnic University,College Sophomore,Handshake Alena,Stouther,
[email protected],Pentagon City Community College,College Sophomore,Handshake Lemmy,Greenroad,
[email protected],Ballston College,College Junior,Handshake Caro,Joder,
[email protected],University of Arlington,College Junior,Handshake Paulie,Leatham,
[email protected],Pentagon City Community College,College Sophomore,Handshake Dickie,Conaghy,
[email protected],Crystal City Community College,College Sophomore,Handshake Chauncey,Espadero,
[email protected],Rosslyn Polytechnic University,College Freshman,Handshake Chelsie,Basini-Gazzi,
[email protected],Crystal City Community College,College Sophomore,Handshake Marabel,Kieran,
[email protected],Pentagon City Community College,College Sophomore,Handshake Ned,Jeanel,
[email protected],Rosslyn Polytechnic University,College Junior,Handshake Benson,Matteris,
[email protected],Rosslyn Polytechnic University,College Freshman,Other Chester,Fearey,
[email protected],Ballston College,College Freshman,Other Arnoldo,Schoenrock,
[email protected],Rosslyn Polytechnic University,College Sophomore,Handshake Wit,McCluskey,
[email protected],Ballston College,College Senior,Handshake Cooper,Strode,
[email protected],Rosslyn Polytechnic University,College Senior,Handshake Cymbre,Hadny,
[email protected],Clarendon State University,College Senior,Handshake Hildagard,Aggio,
[email protected],University of Arlington,College Senior,Handshake Jolynn,Stickels,
[email protected],Pentagon City Community College,College Sophomore,Handshake Danica,Toma,
[email protected],Clarendon State University,College Sophomore,Handshake Mavra,Hullins,
[email protected],Crystal City Community College,College Freshman,Referral Jae,Capin,
[email protected],Crystal City Community College,College Freshman,Handshake Daile,Daugherty,
[email protected],University of Arlington,College Freshman,Handshake Elisabeth,Wittier,
[email protected],Clarendon State University,College Freshman,Handshake Dalt,Maybury,
[email protected],Crystal City Community College,College Sophomore,Handshake Dynah,Vaudin,
[email protected],Pentagon City Community College,College Sophomore,Handshake Edna,Paszek,
[email protected],University of Arlington,College Freshman,Handshake Calli,Corbitt,
[email protected],Rosslyn Polytechnic University,College Sophomore,Handshake Gaspar,Brandacci,
[email protected],Rosslyn Polytechnic University,College Senior,Social Media Claudina,Berecloth,
[email protected],Clarendon State University,College Senior,Handshake Finn,Heaffey,
[email protected],Rosslyn Polytechnic University,College Junior,Handshake Ingrim,Whyke,
[email protected],Rosslyn Polytechnic University,College Senior,Handshake Isadore,Tate,
[email protected],Rosslyn Polytechnic University,College Freshman,Handshake Rowen,Forward,
[email protected],Rosslyn Polytechnic University,College Junior,Handshake id,fname,lname,email,birthdate,gender,ethnicity,veteran_status,disabled_status,international_student,aid_recipient,prior_intern,date_registration_recieved,date_registration_complete 463399,Rosabel,Beckingham,
[email protected],1/27/03,Female,Hispanic or Latinx,No,No,No,Yes,No,8/3/20,8/20/20 463400,Therine,Cheng,
[email protected],8/4/03,Female,Asian American or Asian,No,No,Yes,No,No,8/3/20,8/17/20 463401,Lyndsie,Ferroni,
[email protected],3/22/01,Female,White or Caucasian,No,No,No,Yes,No,8/3/20,8/19/20 463402,Lonnard,De Witt,
[email protected],2/26/02,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/5/20 463403,Agace,Jebb,
[email protected],5/7/02,Female,Middle Eastern,No,No,No,Yes,No,8/3/20,8/8/20 463404,Norby,Pattillo,
[email protected],9/11/01,Male,Pacific Islander,No,No,No,Yes,No,8/3/20,8/19/20 463405,Minni,Gilbey,
[email protected],6/3/03,Female,African American or Black,No,No,No,Yes,No,8/3/20,8/7/20 463406,Luis,Aldrin,
[email protected],1/5/00,Male,Hispanic or Latinx,No,No,No,Yes,No,8/3/20,8/6/20 463407,Jacquelyn,Sandom,
[email protected],11/1/00,Female,American Indian or Alaska Native,No,No,No,Yes,No,8/3/20,8/15/20 463408,Cissiee,Tapner,
[email protected],12/13/00,Female,Asian American or Asian,No,No,No,Yes,No,8/3/20,8/14/20 463409,Noella,Whitehead,
[email protected],9/8/02,Female,African American or Black,No,No,No,No,No,8/3/20,8/3/20 463410,Britt,Folley,
[email protected],3/2/02,Male,African American or Black,No,No,No,Yes,No,8/3/20,8/12/20 463411,Ephraim,Feetham,
[email protected],1/18/02,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/16/20 463412,Bink,Stallan,
[email protected],7/18/01,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/13/20 463413,Dorthea,Eade,
[email protected],7/20/03,Female,African American or Black,No,No,No,Yes,No,8/3/20,8/11/20 463414,Willi,Haw,
[email protected],11/26/03,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/8/20 463415,Rica,Bernardo,
[email protected],4/6/01,Female,Hispanic or Latinx,No,No,No,Yes,No,8/3/20,8/16/20 463416,Elianore,Chasmer,
[email protected],4/11/00,Female,African American or Black,No,No,No,Yes,No,8/3/20,8/11/20 463417,Raymond,Tulleth,
[email protected],10/3/03,Male,African American or Black,No,No,No,Yes,No,8/3/20,8/6/20 463418,Britteny,Gloster,
[email protected],5/5/01,Female,White or Caucasian,No,No,No,Yes,No,8/3/20,8/12/20 463419,Hamnet,Plowright,
[email protected],12/1/00,Male,American Indian or Alaska Native,No,No,No,Yes,No,8/3/20,8/3/20 463420,Jorge,Cuzen,
[email protected],2/7/03,Male,Hispanic or Latinx,No,No,No,Yes,No,8/3/20,8/5/20 463421,Norri,McNeachtain,
[email protected],2/5/02,Female,White or Caucasian,No,No,No,Yes,No,8/3/20,8/14/20 463422,Charissa,Hoggetts,
[email protected],3/10/03,Female,African American or Black,No,No,No,Yes,No,8/3/20,8/10/20 463423,Tobit,Brittoner,
[email protected],5/13/03,Prefer not to say,Prefer not to say,No,No,No,Yes,No,8/3/20,8/9/20 463424,Sadella,Tytcomb,
[email protected],11/4/02,Female,Middle Eastern,No,No,No,Yes,No,8/3/20,8/19/20 463425,Vernice,Mc Gee,
[email protected],5/7/01,Female,White or Caucasian,No,No,No,Yes,No,8/3/20,8/18/20 463426,Kittie,Beig,
[email protected],4/14/03,Female,White or Caucasian,No,No,No,Yes,No,8/3/20,8/17/20 463427,Shaughn,Kemery,
[email protected],4/2/02,Male,African American or Black,No,No,No,Yes,No,8/3/20,8/9/20 463428,Antons,Margetson,
[email protected],2/20/02,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/13/20 463429,Haley,Duckhouse,
[email protected],6/18/01,Female,Asian American or Asian,No,No,No,Yes,No,8/3/20,8/14/20 463430,Tandi,Spadeck,
[email protected],6/23/00,Female,Asian American or Asian,No,No,No,Yes,No,8/3/20,8/6/20 463431,Corney,Gorry,
[email protected],6/18/01,Male,African American or Black,No,No,No,Yes,No,8/3/20,8/12/20 463432,Audre,Lockhead,
[email protected],7/26/03,Female,African American or Black,Yes,No,No,Yes,No,8/3/20,8/13/20 463433,Lemmy,Greenroad,
[email protected],7/15/00,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/15/20 463434,Caro,Joder,
[email protected],8/31/01,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/10/20 463435,Chauncey,Espadero,
[email protected],10/22/03,Male,Hispanic or Latinx,No,No,No,Yes,No,8/3/20,8/9/20 463436,Ned,Jeanel,
[email protected],9/19/01,Male,White or Caucasian,Yes,No,No,Yes,No,8/3/20,8/16/20 463437,Benson,Matteris,
[email protected],3/8/00,Male,African American or Black,No,No,No,Yes,No,8/3/20,8/4/20 463438,Chester,Fearey,
[email protected],1/5/03,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/9/20 463439,Arnoldo,Schoenrock,
[email protected],8/11/03,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/14/20 463440,Wit,McCluskey,
[email protected],6/25/03,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/5/20 463441,Cooper,Strode,
[email protected],2/16/02,Male,White or Caucasian,No,No,No,Yes,No,8/3/20,8/20/20 463442,Cymbre,Hadny,
[email protected],6/3/03,Female,White or Caucasian,No,No,No,No,No,8/3/20,8/12/20 463443,Hildagard,Aggio,
[email protected],5/27/02,Female,White or Caucasian,No,No,No,No,No,8/3/20,8/8/20 463444,Jolynn,Stickels,
[email protected],7/9/00,Female,White or Caucasian,No,No,No,Yes,No,8/3/20,8/20/20 463445,Danica,Toma,
[email protected],12/20/03,Female,White or Caucasian,No,No,No,Yes,No,8/3/20,8/19/20 463446,Calli,Corbitt,
[email protected],9/6/03,Female,White or Caucasian,No,No,No,Yes,No,8/3/20,8/13/20 463447,Gaspar,Brandacci,
[email protected],6/24/00,Male,White or Caucasian,No,No,No,No,No,8/3/20,8/3/20 463448,Claudina,Berecloth,
[email protected],3/11/03,Female,White or Caucasian,No,No,No,Yes,No,8/3/20,8/7/20