Consolidate (Append) two dynamic array formulas in excel

user_140187 0
Viewed 3107

How to combine two dynamic array formula (like filter functions) one below another?

=FILTER(A5:B14,A5:A14>5)
=FILTER(Sheet2!A5:B14,Sheet2!A5:A14>8)

I want the results to come one below another irrespective of number of rows picked up. Is it possible ?

office365 dynamic-arrays excel-formula excel
3 Answers

This isn't very easy too achieve unfortunately. I guess one could try use LAMBDA() but it can also be achieved using FILTERXML() and TEXTJOIN() when these FILTER() is applied as per your example:

enter image description here

Formula in G5:

=LET(X,FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,FILTER(A5:B14,B5:B14>5,""),FILTER(D5:E14,E5:E14>5,""))&"</s></t>","//s"),INDEX(X,SEQUENCE(COUNTA(X)/2,2)))

I used LET() but I guess you could also add two COUNTIFS() instead.

user_97581940

Using the solution proposed in this answer, you join miltiple ranges or dynamic array formulas with this formula, replacing YOUR_RANGES with the ranges or formulas you wish to join:

=FILTERXML("<A><B>" & TEXTJOIN("</B><B>",TRUE,YOUR_RANGES) & "</B></A>", "//B")

In your case, you can split the filter to get only column A in the first result column, and only column B for the second result column:

RESULT SHEET A1 CELL:

=FILTERXML("<A><B>" & TEXTJOIN("</B><B>",TRUE, FILTER(A5:A14,$A5:$A14>5); FILTER(Sheet2!A5:A14,Sheet2!$A5:$A14>8) ) & "</B></A>", "//B")

RESULT SHEET B1 CELL:

=FILTERXML("<A><B>" & TEXTJOIN("</B><B>",TRUE, FILTER(B5:B14,$A5:$A14>5); FILTER(Sheet2!B5:B14,Sheet2!$A5:$A14>8) ) & "</B></A>", "//B")

This will join the 2 filters, one beneath the other, although each column will have a separate dynamic array.

user_49326830

Found an excellent answer (it was also provided as a comment to the question, but I believe it merits being a full answer) in the form of a lambda UDF by Dave Burns at ExcelJet: https://exceljet.net/formula/lambda-append-range

=LAMBDA(range1,range2,default,
  LET(
  rows1,ROWS(range1),
  rows2,ROWS(range2),
  cols1,COLUMNS(range1),
  cols2,COLUMNS(range2),
  rowindex,SEQUENCE(rows1+rows2),
  colindex,SEQUENCE(1,MAX(cols1,cols2)),
  result,
  IF(
    rowindex<=rows1,
    INDEX(range1,rowindex,colindex),
    INDEX(range2,rowindex-rows1,colindex)
  ),
  IFERROR(result,default)
  )
)
user_347040
Related