Forum Discussion

KanwalNo1's avatar
KanwalNo1
Iron Contributor
Jun 21, 2021

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 corr...
Formulas and Functions
office 365
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 21, 2021

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)
)
  • KanwalNo1's avatar
    KanwalNo1
    Iron Contributor
    Jun 21, 2021

    =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.ConsignorTruck NumberName of Registered OwnerBalance
    0000               -  
    41ShreeCLCG16CF0874Raj Kumari Rai               -  
    42ShreeCLCG10AP8471Ramesh Kumar Kashyap S/o Lakhan Lal Kashyap               -  
    43ShreeCLMP65H0290Suresh Kumar Pandey S/o RM Budhsen Pandey               -  
    44ShreeCLCG04LU1753Ashish Kumar Kesharwani               -  
    45ShreeCLCG04LE7276Sumit Kesharwani S/o Bhagwat Kesharwani               -  
    46ShreeCLCG04LY6233Ankit Agrawal S/o Raghuveer Prasad               -  
  • KanwalNo1's avatar
    KanwalNo1
    Iron Contributor
    Jun 21, 2021

    SergeiBaklan It is working perfectly except when oddk = 0 and/or evenk = 0

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 21, 2021

      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"

       

Resources