Greetings,

I have a single Excel 2013 workbook containing 2 tables being pulled from 2 SharePoint lists.  This all works without issue.

One list contains all employees and the other list contains a form for each employee.  I would like to create a third sheet that contains a list of all employees who have not created a form.  I do have a relationship created between the two lists which is Employee ID.

What would be the best way to create this?  I would like it to be dynamic; meaning, the resulting comparison would update when I refresh the data connections.

Thank you!

Bob

I have assumed your Employee IDs are in column A of a sheet named "All Employees" and those IDs of employees who have created a form are on a sheet named "Employee Forms" also in column A.

In cell A2 of your third sheet, array enter (enter using Ctrl-Shift-Enter) the formula

=IFERROR(INDEX('All Employees'!A:A,SMALL(IF(ISERROR(MATCH(OFFSET('All Employees'!$A$1,0,0,COUNTA('All Employees'!A:A),1),'Employee Forms'!A:A,FALSE)),ROW(OFFSET('All Employees'!$A$1,0,0,COUNTA('All Employees'!A:A),1))),ROW(A1))),"")

and copy down until it starts returning blanks.  That will return the Employee IDs of those without forms, and you can use those IDs with VLOOKUP functions to return any other information you want for your third table.

A second method is to use a formula like this in a column on sheet "All Employees"  starting in row 2:

=IF(ISERROR(MATCH(A2,'Employee Forms'!A:A,FALSE)),"No Form","OK")

Then copy down, and you can use data filters to show "No Form" to highlight those without a form.

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor 12 hours 27 minutes ago

I have assumed your Employee IDs are in column A of a sheet named "All Employees" and those IDs of employees who have created a form are on a sheet named "Employee Forms" also in column A.

In cell A2 of your third sheet, array enter (enter using Ctrl-Shift-Enter) the formula

=IFERROR(INDEX('All Employees'!A:A,SMALL(IF(ISERROR(MATCH(OFFSET('All Employees'!$A$1,0,0,COUNTA('All Employees'!A:A),1),'Employee Forms'!A:A,FALSE)),ROW(OFFSET('All Employees'!$A$1,0,0,COUNTA('All Employees'!A:A),1))),ROW(A1))),"")

and copy down until it starts returning blanks.  That will return the Employee IDs of those without forms, and you can use those IDs with VLOOKUP functions to return any other information you want for your third table.

A second method is to use a formula like this in a column on sheet "All Employees"  starting in row 2:

=IF(ISERROR(MATCH(A2,'Employee Forms'!A:A,FALSE)),"No Form","OK")

Then copy down, and you can use data filters to show "No Form" to highlight those without a form.

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor Wednesday, July 29, 2015 7:05 PM

I have assumed your Employee IDs are in column A of a sheet named "All Employees" and those IDs of employees who have created a form are on a sheet named "Employee Forms" also in column A.

In cell A2 of your third sheet, array enter (enter using Ctrl-Shift-Enter) the formula

=IFERROR(INDEX('All Employees'!A:A,SMALL(IF(ISERROR(MATCH(OFFSET('All Employees'!$A$1,0,0,COUNTA('All Employees'!A:A),1),'Employee Forms'!A:A,FALSE)),ROW(OFFSET('All Employees'!$A$1,0,0,COUNTA('All Employees'!A:A),1))),ROW(A1))),"")

and copy down until it starts returning blanks.  That will return the Employee IDs of those without forms, and you can use those IDs with VLOOKUP functions to return any other information you want for your third table.

A second method is to use a formula like this in a column on sheet "All Employees"  starting in row 2:

=IF(ISERROR(MATCH(A2,'Employee Forms'!A:A,FALSE)),"No Form","OK")

Then copy down, and you can use data filters to show "No Form" to highlight those without a form.

Hi Bernie,

So there is no way to do this with PowerPivot?  My data odel has both tables in it and the employee id relationship.

Thank you...

Bob

Hi Bob,

Based on your description, I suggest you use Power Query to get your result.

You can refer to the article and the video about how to use Power Query:

https://www.youtube.com/watch?v=GOfoM0hwVkQ
https://support.office.com/en-us/article/Combine-data-from-multiple-data-sources-Power-Query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Please check if this method works for you, I'm glad to help and follow up your reply.

Regards,

Emi Zhang
TechNet Community Su

Hi Bob,

Based on your description, I suggest you use Power Query to get your result.

You can refer to the article and the video about how to use Power Query:

https://www.youtube.com/watch?v=GOfoM0hwVkQ
https://support.office.com/en-us/article/Combine-data-from-multiple-data-sources-Power-Query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Please check if this method works for you, I'm glad to help and follow up your reply.

Regards,

Emi Zhang
TechNet Community Su

You need to put a column of formulas titled "Lacks Form" into your whole list of employees, along the lines of

=ISERROR(MATCH(A2,'Employee Forms'!A:A,FALSE))

And then use TRUE in that field as the basis of your extract.

You need to put a column of formulas titled "Lacks Form" into your whole list of employees, along the lines of

=ISERROR(MATCH(A2,'Employee Forms'!A:A,FALSE))

And then use TRUE in that field as the basis of your extract.

Hi Bernie,

The original lists, employees and forms, is being derived from SharePoint lists.  Using PowerQuery, I have created 2 tables in Excel; one for all employees and one for all forms.

Using your approach, from the employees table, how does it look up the matching form?

Bob

If you are placing the lists of forms somewhere, then you should have a column of IDs, which I have assumed to be column A of a sheet named 'Employee Forms'

And you have another list of all employees, which I have again assumed is in column A. You can prefill a column of formulas if that list changes length, modifying the previous formula to

=AND(A2<>"",ISERROR(MATCH(A2,'Employee Forms'!A:A,FALSE)))

Copy that down for more rows than you expect employees, and it will report TRUE for those IDs that are not blank and that are not included in your other list.

If you are placing the lists of forms somewhere, then you should have a column of IDs, which I have assumed to be column A of a sheet named 'Employee Forms'

And you have another list of all employees, which I have again assumed is in column A. You can prefill a column of formulas if that list changes length, modifying the previous formula to

=AND(A2<>"",ISERROR(MATCH(A2,'Employee Forms'!A:A,FALSE)))

Copy that down for more rows than you expect employees, and it will report TRUE for those IDs that are not blank and that are not included in your other list.

• Proposed as answer by Emi Zhang CHNMicrosoft contingent staff, Moderator Thursday, August 06, 2015 2:40 AM
• Marked as answer by George.Zhao CHNMicrosoft contingent staff, Moderator Thursday, August 06, 2015 7:04 AM

If you are placing the lists of forms somewhere, then you should have a column of IDs, which I have assumed to be column A of a sheet named 'Employee Forms'

And you have another list of all employees, which I have again assumed is in column A. You can prefill a column of formulas if that list changes length, modifying the previous formula to

=AND(A2<>"",ISERROR(MATCH(A2,'Employee Forms'!A:A,FALSE)))

Copy that down for more rows than you expect employees, and it will report TRUE for those IDs that are not blank and that are not included in your other list.

Hi Bernie,

I'm back working on this now.

I went to the sheet where the Employee list is loaded via ODC connection (from SharePoint).  I inserted a column in the sheet (column B).  Then I attempted to add the formula.  However, the cell simply displays the formula text, not the formula result.  What am I doing wrong?

Thank you.

Bob