sort/match columns on same value and skip missing

[Melgro]

Hello,

Im new here and i've got a question. I'm making a yearbook for students and i want to check if everyone has filled in the forms enquete.
I download a forms sheet with the input from the students and now i want sort the data with my other excel (The excel with al the names)
How can i compare / sort the excels to see who hasn't send something and sort the 2 data's?

I added a example, hope you can help me. Thanks!

 

[Eric W]

Welcome to MrExcel!

You can do that with a VLOOKUP:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Class							Forms
2	StudentNR	name	Message 1	Message 2				StudentNR	Message 1	Message 2
3	20	student1	ex1e	ex2e				22	ex1a	ex2a
4	21	student2						25	ex1b	ex2b
5	22	student3	ex1a	ex2a				26	ex1c	ex2c
6	23	student4						24	ex1d	ex2d
7	24	student5	ex1d	ex2d				20	ex1e	ex2e
8	25	student6	ex1b	ex2b
9	26	student7	ex1c	ex2c
10
11
Sheet6

Cell Formulas
Range	Formula
C3:C9	C3	=IFERROR(VLOOKUP($A3,$H$3:$J$20,2,0),"")
D3:D9	D3	=IFERROR(VLOOKUP($A3,$H$3:$J$20,3,0),"")

You just need to adjust the range to point to where the messages are. It can be on another sheet, or even another workbook. Depending on how your workbook(s) are set up, a small macro might work too.