VLOOPUP to compare 2 spreadsheets

[Mojosixx]

I need to compare 2 spreadsheets and identify the employees missing from one of them. The first spreadsheet is a list of our employees. The second spreadsheet is a list of employees that attended a training in October 2017. Both spreadsheets include the employees unique badge number. Can I use VLOOPUP to identify what employees did not attend?

 

[mrshl9898]

Yes, Just VLOOKUP the all sheet against the attended. N/A's will be those that didn't attend.

=LOOKUP(cell with employee name, column containing employees,1,0)

 

[NiMip]

Hi there, you could use a VLOOKUP, but personally I would suggest a MATCH function as follows, with red names indicating a cell or range you'll have to enter yourself according to your data:

=IF(ISNUMBER(MATCH(badgeNumber,trainingBadgeNoColumn,0)),"Attended training","Did not attend training")

 

[Aladin Akyurek]

@Mojosixx

Let column A of Sheet1 the list of employees (badge numbers) and column A of Sheet2 employees (badge numbers) who attended the October 2017 training.

In B2 of Sheet1 enter and copy down:

=1-ISNUMBER(MATCH($A2,Sheet2!A:A,0))

This tags non-attenders with a 1, attenders with 0.

Using VLOOKUP here could be awkward; LOOKUP is not applicable unless Sheet2!A:A is sorted in ascending order.

 

[Mojosixx]

@Mojosixx

Let column A of Sheet1 the list of employees (badge numbers) and column A of Sheet2 employees (badge numbers) who attended the October 2017 training.

In B2 of Sheet1 enter and copy down:

=1-ISNUMBER(MATCH($A2,Sheet2!A:A,0))

This tags non-attenders with a 1, attenders with 0.

Using VLOOKUP here could be awkward; LOOKUP is not applicable unless Sheet2!A:A is sorted in ascending order.

Hi Aladin & everyone offering help,

I tried the above and it indicated everyone as a non-attender (1) although I know 2015 of them attended. I'm sure it's user error but I'm not sure what I'm doing wrong.

 

[Aladin Akyurek]

Hi Aladin & everyone offering help,

I tried the above and it indicated everyone as a non-attender (1) although I know 2015 of them attended. I'm sure it's user error but I'm not sure what I'm doing wrong.

Care to post the formula as you implemented it?

 

[Mojosixx]

Care to post the formula as you implemented it?

Here is the current set-up.

Worksheet 1 titled "Employees" is a list of all employee badge numbers starting in A2.
Worksheet 2 titled "Attendance" is a list of the badge numbers for the employees who attended the training, starting in A2 as well.

In cell B2 of worksheet one I pasted the following formula:

=1-ISNUMBER(MATCH($A2,Sheet2!A:A,0))

 

[mrshl9898]

=1-ISNUMBER(MATCH($A2,Attendance!A:A,0))

 

[Mojosixx]

=1-ISNUMBER(MATCH($A2,Attendance!A:A,0))

Still resulting in 1's for all records. There should be around 250 people with 0's as they didn't attend.

 

[mrshl9898]

Do you have any trailing spaces or "half spaces"? try =TRIM(A2) on both columns and repaste the data in.