Tidying up array formulae

[tigerzen]

The formulae in F6 produces the solution needed however it also contains blank spaces and zeroes. I've had this happen before with array formulae, how can we clean this formula up?

Football stats functions.xlsx
	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
Sheet17

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.

 

[Georgiboy]

I would imagine there are many ways to do this, below is one:

Book1
	A	B	C	D	E	F	G
1	Group 1			Group 2
2	Jack	Ken		Peter	Vic
3	Tom	Sally		Tom	Fred
4	Peter	Vic		Jane	Milton
5	Jane			Sally
6							Fred
7							Milton
Sheet1

Cell Formulas
Range	Formula
G6:G7	G6	=LET(tc,TOCOL(D2:E5,1),FILTER(tc,COUNTIF(A2:B5,tc)=0))
Dynamic array formulas.

 

[tigerzen]

Thanks Georgiboy, I work with data that is spread over several columns, the TOCOL formula is great when you can move all the data into a single column but if you can't what are the alternatives?

 

[Peter_SSs]

Thanks Georgiboy, I work with data that is spread over several columns, the TOCOL formula is great when you can move all the data into a single column but if you can't what are the alternatives?

Doesn't the suggested formula do exactly what you asked?

Isn't this the same question as asked here? Extracting items not on both lists

 

[tigerzen]

Doesn't the suggested formula do exactly what you asked?

Isn't this the same question as asked here? Extracting items not on both lists

Thanks Peter, I guess it was more of a general question as to how to convert partial solutions (ie contain TRUE, FALSE, 0) into a meaningful list.

 

[Peter_SSs]

.. it was more of a general question ..

In that case I don't think that there is just one 'general' answer. It depends on the type of original data (text, numerical, mixed) and just what result & layout is required in the particular circumstance