CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Andere
Andere

Reputation: 11

How to put one column of an Excel sheet, into another one, based on the same unique identifier

I have two sheets in Excel. One sheet with these column names:

Student ID, Telephone Number, City, Address, Preference 1, Preference 2

Entry date, Student ID, City, Availability, Preference 1, Preference 2

So, in the first sheet, Preference 1 and 2 are blank. The Student ID's are not in the same order in sheet 2 as in sheet 1. I need to put Preference 1 and 2 from sheet 2 in sheet 1, based on the student ID. How can I do that?

I tried to do it manually, but this takes a lot of work so I was wondering if there was an easier way to do this that I do not know of. I am new to Excel so I don't know about any formulas or where to put those formulas. I have looked at other topics in Stackoverflow too, but these were more difficult than my problem and the solutions seemed difficult as well (I didn't understand them). If someone knows a solution, then please say so!

Upvotes: 1

Views: 642

Answers (1)

Spencer Barnes
Spencer Barnes

Reputation: 2877

In this answer I have assumed that

  • your sheets are named Sheet1 and Sheet2,
  • The column names you've given are in order and start in column A
  • Row 1 contains column headers, data starts from Row 2

If the above is true, in Sheet1 Cell E2 (the first blank cell in 'Preference 1') type:

=VLOOKUP(A2, 'Sheet2'!B:F, 4, FALSE)

And in the cell next to that type the same, but replace the 4 with a 5. The cells should auto-populate with the relevant data.

Then, select both of those cells you've just filled in so that they get a green outline showing they're selected, and double-click the little square at the bottom-right of the green outline to flash-fill that formula down your dataset.

(obviously if any of my assumptions were wrong, you'll need to edit the process and/or formula accordingly)

Upvotes: 2

Related Questions