Compare 2 lists in Excel and List the Differences

[AngleseyExcel]

I'm a little stuck on a project.

Sheet 1 has a list of people
Sheet 2 has a list of people

Sheet 3 needs to compare the lists in Sheets 1 and 2 and list the differences between them.

I'm not sure of the syntax, and how to write the indirect formula to do this ?

Any help would be greatly appreciated.

p.s. i'd rather not use conditional formatting for this.

 

[AngleseyExcel]

Yes the rest look ok.

I've tried changing the quotes, but getting an error, any idea what i'm doing wrong ?

=FILTER(INDIRECT("’"&B2&"’"!D10:D61),(COUNTIFS(INDIRECT("’"&B2&"’"!A10:A61),INDIRECT("’"&B2&"’"!D10:D61))=0)*(INDIRECT("’"&B2&"’"!A2:A100<>"")),"")

 

[AngleseyExcel]

Yes the rest look ok.

I've tried it, and still getting an error. Any idea where i'm going wrong ?

=FILTER(INDIRECT("’"&B2&"’"!D10:D61),(COUNTIFS(INDIRECT("’"&B2&"’"!A10:A61),INDIRECT("’"&B2&"’"!D10:D61))=0)*(INDIRECT("’"&B2&"’"!A2:A100<>"")),"")

 

[kweaver]

Why do you need to use INDIRECT when the previously posted example (post #4) seemed to work if adjusted for the specific rows and columns?

 

[Fluff]

Try it like

=FILTER(INDIRECT("’"&B2&"'!D10:D61"),(COUNTIFS(INDIRECT("’"&B2&"’!A10:A61"),INDIRECT("’"&B2&"’!D10:D61"))=0)*(INDIRECT("’"&B2&"’!A2:A100")<>""),"")

 

[AngleseyExcel]

Why do you need to use INDIRECT when the previously posted example (post #4) seemed to work if adjusted for the specific rows and columns?

Because the sheet name in B2 will change, so i want it to compare the lists in the same positions on other sheets. I know what i want to do, just not how to write the formula for it.