Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Assigning IDs from one file to a list of students in another file

0

I have generated an excel spreadsheet from our school SIS that includes multiple lines for each student in our school.  In this spreadsheet, each student has an ID assigned, but unfortunately it's not the unique ID that I need on  the spreadsheet.  I have a separate spreadsheet that lists each student and includes the ID on the first spreadsheet as well as the unique ID I need.  Is there a formula I can use to read the unique IDs from the second spreadsheet and place them in the first?  I'm thinking some sort of "if/then" statement, but with 600 students I'm not sure how to do that. 

Thanks in advance for your help!

excel
MS Office Version: Office 365
posted by: KateTemple on Wed May 2, 2018 at 8:45 am
Answer
Discuss

Discussion

If you only have unique ID for one set of data, how do you intend to match them? It seems to me like you will have to match the values using the names of the students and then, if there are duplicates, go through them by hand. Unless, is the ID that is on both spreadsheets for each student ALSO unique to each student?  A sample set of data would make it much easier to give you a specific answer - you can edit your question and upload a sample file with dummy data.
don (rep: 1989) May 2, '18 at 11:37 am
Add to Discussion

Answers

0

Excel's VLOOKUP() function is designed for this kind of work. When deployed it might look like this. (Copy down as required.)

=VLOOKUP($A2,'Students List'!$A$2:$D$8,4,FALSE)

The key to understanding this example is the Range A2:D8 on the sheet "Students List". It contains both the wrong number you have and the correct ID you want. The wrong number must be in the first column of that range. To achieve this you may have to create a temporary copy of that column.

In the above formula, $A2 is in the sheet where you want to have the correct ID. It contains the wrong ID which the function finds in the first column of the specified range and then returns the value found in the 4th column of the row where a match was found. The function will return an error if no match is found.

To break the link with the Students List copy the column with the formula and Pste Special > Values in place. The formulas will be replaced with the values they extracted from the Students sheet.

The whole thing is demonstrated in the attached workbook. I changed the name of the Students List sheet above to show the apostrophs which must surround the sheet's name if it contains blanks.

posted by: Variatus (rep: 4889) on Wed May 2, 2018 at 8:49 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login