Forum Discussion
Join the results of two filtered tables in a single range
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 |
4 Replies
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) )=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 oddarraySr.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 - SergeiBaklan It is working perfectly except when oddk = 0 and/or evenk = 0
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"
