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.

 

[kweaver]

What do you mean by "differences" ? Are you looking for a match or non-match?

 

[alansidman]

Are you able to show some sample data of before and after so we have a better understanding of your needs. 8-10 records should suffice.

 

[AhoyNC]

Maybe something like this:
Formula in columns D and E will work in any version of Excel from 2010 and up. Drag the formulas down the columns as needed.
Formulas in cells G and H will only work in Excel 365.

compare lists w forum or cond formating.xlsx
	A	B	C	D	E	F	G	H
1	List 1	List 2		Additional Names	Names removed		Additional Names	Names removed
2	Sam	Fred		on List 2	from List1		on List 2	from List1
3	Mike	Mike		Fred	Sam		Fred	Sam
4	Mary	June		June			June
5	Judy	Mary		Dawn			Dawn
6		Judy
7		Dawn
Sheet3

Cell Formulas
Range	Formula
G3:G5	G3	=FILTER(B2:B7,ISNA(MATCH($B$2:$B$7,$A$2:$A$5,0)),"")
H3	H3	=FILTER($A$2:$A$5,ISNA(MATCH(A2:A5,B2:B7,0)),"")
E3:E6	E3	=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($B$2:$B$16)-ROW($B$2)+1)/(ISNA(MATCH($A$2:$A$5,$B$2:$B$16,0))),ROWS($D$3:D3))),"")
D3:D7	D3	=IFERROR(INDEX($B$2:$B$7,AGGREGATE(15,6,(ROW($B$2:$B$7)-ROW($B$2)+1)/(ISNA(MATCH($B$2:$B$7,$A$2:$A$10,0))),ROWS($D$3:D3))),"")
Dynamic array formulas.

 

[AngleseyExcel]

Maybe something like this:
Formula in columns D and E will work in any version of Excel from 2010 and up. Drag the formulas down the columns as needed.
Formulas in cells G and H will only work in Excel 365.

compare lists w forum or cond formating.xlsx
	A	B	C	D	E	F	G	H
1	List 1	List 2		Additional Names	Names removed		Additional Names	Names removed
2	Sam	Fred		on List 2	from List1		on List 2	from List1
3	Mike	Mike		Fred	Sam		Fred	Sam
4	Mary	June		June			June
5	Judy	Mary		Dawn			Dawn
6		Judy
7		Dawn
Sheet3

Cell Formulas
Range	Formula
G3:G5	G3	=FILTER(B2:B7,ISNA(MATCH($B$2:$B$7,$A$2:$A$5,0)),"")
H3	H3	=FILTER($A$2:$A$5,ISNA(MATCH(A2:A5,B2:B7,0)),"")
E3:E6	E3	=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($B$2:$B$16)-ROW($B$2)+1)/(ISNA(MATCH($A$2:$A$5,$B$2:$B$16,0))),ROWS($D$3:D3))),"")
D3:D7	D3	=IFERROR(INDEX($B$2:$B$7,AGGREGATE(15,6,(ROW($B$2:$B$7)-ROW($B$2)+1)/(ISNA(MATCH($B$2:$B$7,$A$2:$A$10,0))),ROWS($D$3:D3))),"")
Dynamic array formulas.

This is similar to what i need, but in my workbook, List 1 is in Sheet 1, List 2 is in Sheet 2, but i want the result of the additional names as you've shown above to be in Sheet 3. I'm just unsure of where i'd need to place the indirect function.

 

[Fluff]

Assuming both lists are in col A, how about

=FILTER(Sheet2!A2:A100,(COUNTIFS(Sheet1!A2:A100,Sheet2!A2:A100)=0)*(Sheet2!A2:A100<>""),"")

 

[AngleseyExcel]

Assuming both lists are in col A, how about

=FILTER(Sheet2!A2:A100,(COUNTIFS(Sheet1!A2:A100,Sheet2!A2:A100)=0)*(Sheet2!A2:A100<>""),"")

I've tried modify it using the the indirect function, but i've made a mistake somewhere, the 2 lists are on the same sheet now. The sheet name is in Cell B2.

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

 

[Fluff]

You are using the wrong type of quotes, they should be " rather than the smart quotes.

 

[AngleseyExcel]

You are using the wrong type of quotes, they should be " rather than the smart quotes.

Ok, is the rest of the syntax correct ? What should it look like ?

 

[Fluff]

Yes the rest look ok.