CODEWITHSUNDEEP
excelexcel-formula
superjoness
superjoness

Reputation: 11

How to match names in two sheets?

I am using excel to keep track of attendance but i find this very difficult. Is there a way/formula for this?

Problem. Sheet 1 column A1-30 will show the list of people for day 1. Contains list of names say (Last name, First name ONLY) Sheet 2 column J1-3000 also contains a list of names but with middle names in it (Last Name, First Name Middle Name)

How can i match two sheets to create a third sheet that will show who attended Day 1?

Thanks a bunch.

Upvotes: 1

Views: 4167

Answers (1)

Andrew
Andrew

Reputation: 7778

use vlookup function to match values Read this: http://chandoo.org/wp/2012/03/30/comprehensive-guide-excel-vlookup/

use the following formula to get rid of the middle name (which is in the following expected format: (Lname,Fname,Mname)

=TRIM(LEFT(A1,(FIND(",",A1,1)-1)))&", "&TRIM(LEFT(MID(A1,FIND(",",A1)+1,100),FIND(",",MID(A1,FIND(",",A1)+1,100))-1))

***please note, it adds the space in between fname and lastname John,Smith,Midname -> John, Smith*

or, as @Craig Q suggested, use =LEFT(A1,FIND(",",A1,FIND(",",A1)+1)-1

Therefore.

  • Step1: Create a new column on Sheet2 (the sheet which lists the middlenames)
  • Step2: Use the formula to get rid of the middle names for all rows
  • Step3: Manually test couple of items in the new column on the Sheet2 and Sheet1 and make sure that they match by using this formula =A1=Sheet2!A1 (please manually find the same records on both sheets when you test your entries!!!!!)
  • Step4: When you sure that entries on both sheets match (Formula in #3 must return TRUE)
  • Step5: Use vlookup using values from Step2 within records on Sheet1

Upvotes: 1

Related Questions