Extract records that are not in either dataset

[tigerzen]

I'm after a formula that extracts entries that are not on either dataset, the filter function gives me a VALUE error, I can only get FILTER to work it out if I have single columns of data. The correct formula would return Jack, Fred, and Milton

Book6
	A	B	C	D	E	F
1	Group 1			Group 2
2	Jack	Ken		Peter	Vic	#VALUE!
3	Tom	Sally		Tom	Fred
4	Peter	Vic		Jane	Milton
5	Jane			Sally
Sheet1 (2)

Cell Formulas
Range	Formula
F2	F2	=FILTER($A$2:$B$5,NOT(COUNTIF($D$2:$E$5,$A$2:$B$5)))

 

[tigerzen]

I have managed a solution in F6 which I'm partially happy with, but how do you remove the array component and the 0 in cell G9? Ideally I'd like the solution to be Fred and Milton in separate cells.

Book6
	A	B	C	D	E	F	G
1	Group 1			Group 2
2	Jack	Ken		Peter	Vic	#VALUE!
3	Tom	Sally		Tom	Fred
4	Peter	Vic		Jane	Milton
5	Jane			Sally
6
7							Fred
8							Milton
9							0
Sheet3

Cell Formulas
Range	Formula
F2	F2	=FILTER($A$2:$B$5,NOT(COUNTIF($D$2:$E$5,$A$2:$B$5)))
F6:G9	F6	=IF(COUNTIF(A2:B5,D2:E5)=0,D2:E5,"")
Dynamic array formulas.

 

[Fluff]

How about

=UNIQUE(TOCOL(A2:E5),,1)

 

[tigerzen]

Thanks Fluff, a most economical and effective solution.

If I may, I have 2 follow-up questions:
1. How would you change the solution if you wanted to list the entries that were common to both lists?
2. I've observed from time to time that with arrays you get solutions as per my post 2 where you have blanks or empty cells, I use TEXTJOIN to combine into one cell but is there a workaround/method to simply list the solution in separate cells?

 

[Fluff]

As both of those are different questions, they need new threads.