Vstack+Filter+ISNumber how to remove blank results from table

[Yugo101]

Good evening.

I basically got the formula to work but in the returned results there is blank & N/A because there are nil results in 1 of my 3 tables.
So helper 1 is below

Ref 1	Code	Name	Ref 2	Code	Name	Ref 3	Code	Name
asdasd	1234	John	asdasd	23123	jim	asdasd	1234	John
asdasd	2341	as	asdasd	12	Jayne	asdasd	12	John
asdasd	5412	Simon	asdasd	23123	Jim	asdasd	23123	simon

On another sheet I have below code which works fine, till find nil result in a table (ref 2)

=UNIQUE(VSTACK(FILTER('Helper Sheet 1'!A2:C5,(ISNUMBER(SEARCH("John",'Helper Sheet 1'!C2:C5)))+(ISNUMBER(SEARCH("Simon",'Helper Sheet 1'!C2:C5))),""),FILTER('Helper Sheet 1'!E2:G5,(ISNUMBER(SEARCH("John",'Helper Sheet 1'!G2:G5)))+(ISNUMBER(SEARCH("Simon",'Helper Sheet 1'!G2:G5))),""),FILTER('Helper Sheet 1'!I2:K5,(ISNUMBER(SEARCH("simon",'Helper Sheet 1'!K2:K5)))+(ISNUMBER(SEARCH("John",'Helper Sheet 1'!K2:K5))),"")))

How can remove the blank row so the result below it moves up?.

Ref	Code	Name
asdasd	1234	John
asdasd	5412	Simon
#N/A	#N/A
asdasd	12	John
asdasd	23123	simon

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Ref 1	Code	Name	Ref 2	Code	Name	Ref 3	Code	Name
2	asdasd	1234	John	asdasd	23123	jim	asdasd	1234	John
3	asdasd	2341	as	asdasd	12	Jayne	asdasd	12	John
4	asdasd	5412	Simon	asdasd	23123	Jim	asdasd	23123	simon
5
6
7	Ref	Code	Name
8	asdasd	1234	John
9	asdasd	1234	John
10	asdasd	12	John
11	asdasd	5412	Simon
12	asdasd	23123	simon
Sheet6

Cell Formulas
Range	Formula
A8:C12	A8	=LET(w,WRAPROWS(TOCOL(A2:I4),3),DROP(REDUCE("",{"John","Simon"},LAMBDA(a,b,VSTACK(a,FILTER(w,ISNUMBER(SEARCH(b,INDEX(w,0,3))))))),1))
Dynamic array formulas.

 

[Fluff]

Another option

=LET(v,VSTACK('Helper Sheet 1'!A2:C5,'Helper Sheet 1'!E2:G5,'Helper Sheet 1'!I2:K5),FILTER(v,(ISNUMBER(SEARCH("John",INDEX(v,,3))))+(ISNUMBER(SEARCH("Simon",INDEX(v,,3)))),""))

 

[Yugo101]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Ref 1	Code	Name	Ref 2	Code	Name	Ref 3	Code	Name
2	asdasd	1234	John	asdasd	23123	jim	asdasd	1234	John
3	asdasd	2341	as	asdasd	12	Jayne	asdasd	12	John
4	asdasd	5412	Simon	asdasd	23123	Jim	asdasd	23123	simon
5
6
7	Ref	Code	Name
8	asdasd	1234	John
9	asdasd	1234	John
10	asdasd	12	John
11	asdasd	5412	Simon
12	asdasd	23123	simon
Sheet6

Cell Formulas
Range	Formula
A8:C12	A8	=LET(w,WRAPROWS(TOCOL(A2:I4),3),DROP(REDUCE("",{"John","Simon"},LAMBDA(a,b,VSTACK(a,FILTER(w,ISNUMBER(SEARCH(b,INDEX(w,0,3))))))),1))
Dynamic array formulas.

Sorry bit of 2 things, if I add blanks columns in-between data and add new rows I seems to pick all the data up. but if don't add blank columns it works perfectly even when add extra rows

 

[Cubist]

Try adding this bit:

=LET(w,WRAPROWS(TOCOL(A2:M4,1),3),DROP(REDUCE("",{"John","Simon"},LAMBDA(a,b,VSTACK(a,FILTER(w,ISNUMBER(SEARCH(b,INDEX(w,0,3))))))),1))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Ref 1	Code	Name			Ref 2	Code	Name			Ref 3	Code	Name
2	asdasd	1234	John			asdasd	23123	jim			asdasd	1234	John
3	asdasd	2341	as			asdasd	12	Jayne			asdasd	12	John
4	asdasd	5412	Simon			asdasd	23123	Jim			asdasd	23123	simon
5
6
7	Ref	Code	Name
8	asdasd	1234	John
9	asdasd	1234	John
10	asdasd	12	John
11	asdasd	5412	Simon
12	asdasd	23123	simon
Sheet1

Cell Formulas
Range	Formula
A8:C12	A8	=LET(w,WRAPROWS(TOCOL(A2:M4,1),3),DROP(REDUCE("",{"John","Simon"},LAMBDA(a,b,VSTACK(a,FILTER(w,ISNUMBER(SEARCH(b,INDEX(w,0,3))))))),1))
Dynamic array formulas.

Note that this method won't work if you have blank in your actual tables. I'd use VSTACK like Fluff suggested.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Ref 1	Code	Name			Ref 2	Code	Name			Ref 3	Code	Name
2	asdasd	1234	John			asdasd	23123	jim			asdasd	1234	John
3	asdasd	2341	as			asdasd	12	Jayne			asdasd	12	John
4	asdasd	5412	Simon			asdasd	23123	Jim			asdasd	23123	simon
5
6
7	Ref	Code	Name
8	asdasd	1234	John
9	asdasd	1234	John
10	asdasd	12	John
11	asdasd	5412	Simon
12	asdasd	23123	simon
Sheet1

Cell Formulas
Range	Formula
A8:C12	A8	=LET(w,VSTACK(A2:C4,F2:H4,K2:M4),DROP(REDUCE("",{"John","Simon"},LAMBDA(a,b,VSTACK(a,FILTER(w,ISNUMBER(SEARCH(b,INDEX(w,0,3))))))),1))
Dynamic array formulas.

 

[Yugo101]

Try adding this bit:

=LET(w,WRAPROWS(TOCOL(A2:M4,1),3),DROP(REDUCE("",{"John","Simon"},LAMBDA(a,b,VSTACK(a,FILTER(w,ISNUMBER(SEARCH(b,INDEX(w,0,3))))))),1))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Ref 1	Code	Name			Ref 2	Code	Name			Ref 3	Code	Name
2	asdasd	1234	John			asdasd	23123	jim			asdasd	1234	John
3	asdasd	2341	as			asdasd	12	Jayne			asdasd	12	John
4	asdasd	5412	Simon			asdasd	23123	Jim			asdasd	23123	simon
5
6
7	Ref	Code	Name
8	asdasd	1234	John
9	asdasd	1234	John
10	asdasd	12	John
11	asdasd	5412	Simon
12	asdasd	23123	simon
Sheet1

Cell Formulas
Range	Formula
A8:C12	A8	=LET(w,WRAPROWS(TOCOL(A2:M4,1),3),DROP(REDUCE("",{"John","Simon"},LAMBDA(a,b,VSTACK(a,FILTER(w,ISNUMBER(SEARCH(b,INDEX(w,0,3))))))),1))
Dynamic array formulas.

Note that this method won't work if you have blank in your actual tables. I'd use VSTACK like Fluff suggested.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Ref 1	Code	Name			Ref 2	Code	Name			Ref 3	Code	Name
2	asdasd	1234	John			asdasd	23123	jim			asdasd	1234	John
3	asdasd	2341	as			asdasd	12	Jayne			asdasd	12	John
4	asdasd	5412	Simon			asdasd	23123	Jim			asdasd	23123	simon
5
6
7	Ref	Code	Name
8	asdasd	1234	John
9	asdasd	1234	John
10	asdasd	12	John
11	asdasd	5412	Simon
12	asdasd	23123	simon
Sheet1

Cell Formulas
Range	Formula
A8:C12	A8	=LET(w,VSTACK(A2:C4,F2:H4,K2:M4),DROP(REDUCE("",{"John","Simon"},LAMBDA(a,b,VSTACK(a,FILTER(w,ISNUMBER(SEARCH(b,INDEX(w,0,3))))))),1))
Dynamic array formulas.

I did like your formula as quick away to add additional people but yeah there will always be blanks sadly in my tables. Fluff's formula worked perfectly with that scenario

 

[Fluff]

Glad we could help & thanks for the feedback.