Join the results of two filtered tables in a single range

[KanwalNo1]

Hi,

I am using the following formula to join the filtered results of two tables.

Filter is resulting in 2 rows from 1st table and 10 rows from 2nd table.

While the results of 1st table are correctly populated, first two rows of the results of the 2nd table are not being populated and instead getting two #N/A rows as last two rows.

=LET(
oddarray,FILTER(INDEX(BDBL2All,SEQUENCE(ROWS(BDBL2All),1),XMATCH(A6:G6,BDBL2Hdr)),IFNA(XMATCH(BDBL304Emami,$D$2)*(BDBL309IncoT="FOR")*ISNA(XMATCH(BDBL308Tptr,OwnVeh[Registered Owner])),0),0),
evenarray,FILTER(INDEX(BDBL2AllS,SEQUENCE(ROWS(BDBL2AllS),1),XMATCH(A6:G6,BDBL2HdrS)),IFNA(XMATCH(BDBL304Shree,$D$2)*(BDBL309IncoTS="FOR")*ISNA(XMATCH(BDBL308TptrS,OwnVeh[Registered Owner])),0),0),
oddK,ROWS(oddarray),
evenK,ROWS(evenarray),
k,SEQUENCE(oddK+evenK),
IF(k<=oddK,oddarray,evenarray)
)

Sr.No.	Consignor	Truck Number	Name of Registered Owner	Balance
376	NuvocoVCL	CG22G9100	Mohd Akhtar	35,291
380	NuVistaL	CG04NA9076	Pritesh Kumar Jaiswal	34,546
498	ShreeCL	MP15HA4691	Amol Singh Thakur	-
499	ShreeCL	CG09JD9997	Rajesh Kesharwani	-
500	ShreeCL	CG09JD9997	Rajesh Kesharwani	-
502	ShreeCL	CG06GM6793	Alok Agrawal S/o Jai Prakash Agrawal	3,400
503	ShreeCL	CG04LT7198	Akhil Kumar Pandey S/o Rajesh Kumar Pandey	-
504	ShreeCL	CG10AU9790	Maa Sharda Traders	25,422
505	ShreeCL	CG11AL9019	Bhanu Sahu S/o Rup Say Sahu	26,500
506	ShreeCL	CG16CK2056	Sumit Kumar Das S/o Gurupad Das	22,245
#N/A	#N/A	#N/A	#N/A	#N/A
#N/A	#N/A	#N/A	#N/A	#N/A

@Sergei Baklan

[#2]

@KanwalNo1 

assuming you have same number of columns, rest of the formula will be as

...
m, SEQUENCE(,COLUMNS(oddarray)),
IF(k <= oddK,
   INDEX(oddarray,  k, m),
   INDEX(evenarray, k-oddK, m)
)

[#3]

@Sergei Baklan It is working perfectly except when oddk = 0 and/or evenk = 0

[#4]

=LET(
oddarray,FILTER(INDEX(BDBL2All,SEQUENCE(ROWS(BDBL2All),1),XMATCH(A6:G6,BDBL2Hdr)),IFNA(XMATCH(BDBL304Emami,$D$2)*(BDBL309IncoT="FOR")*ISNA(XMATCH(BDBL308Tptr,OwnVeh[Registered Owner])),0),0),
evenarray,FILTER(INDEX(BDBL2AllS,SEQUENCE(ROWS(BDBL2AllS),1),XMATCH(A6:G6,BDBL2HdrS)),IFNA(XMATCH(BDBL304Shree,$D$2)*(BDBL309IncoTS="FOR")*ISNA(XMATCH(BDBL308TptrS,OwnVeh[Registered Owner])),0),0),
oddK,ROWS(oddarray),
evenK,ROWS(evenarray),
k,SEQUENCE(oddK+evenK),
m,SEQUENCE(,COLUMNS(oddarray)),
n,SEQUENCE(,COLUMNS(evenarray)),
IF(k<=oddK,
INDEX(oddarray,k,m),
INDEX(evenarray,k-oddK,n))
)

Added n to avoid a single column array, in case oddk = 0
But I am still getting a ZERO valued row for oddarray

Sr.No.	Consignor	Truck Number	Name of Registered Owner	Balance
0	0	0	0	-
41	ShreeCL	CG16CF0874	Raj Kumari Rai	-
42	ShreeCL	CG10AP8471	Ramesh Kumar Kashyap S/o Lakhan Lal Kashyap	-
43	ShreeCL	MP65H0290	Suresh Kumar Pandey S/o RM Budhsen Pandey	-
44	ShreeCL	CG04LU1753	Ashish Kumar Kesharwani	-
45	ShreeCL	CG04LE7276	Sumit Kesharwani S/o Bhagwat Kesharwani	-
46	ShreeCL	CG04LY6233	Ankit Agrawal S/o Raghuveer Prasad	-

[#5]

@KanwalNo1 

You may modify as

oddK,IFERROR(ROWS(oddarray),0),
m,   IFERROR(SEQUENCE(,COLUMNS(oddarray)),
             SEQUENCE(,COLUMNS(oddarray))),
IF((k <= oddK) ,
   INDEX(oddarray,  k, m),
   INDEX(evenarray, k-oddK, m)
)

If both errors are empty you may wrap IF by IFERROR and return something like "empty table"