buakp.blogg.se

Then, after adding "" to the beginning and "" to the end, we have an XML formatted text: the FILTERXML will separate the tags into a dynamic array which will be the joined/appended ranges. The JOINTEXT function grabs your ranges and joins them as a text with the delimiter "". I know its possible to use cell to cell using the formula '', but that only returns the value of one row, I need to return all rows of a column, automatically. You can then apply any dynamic array formula (like UNIQUE) to the result. I need to link the data from an entire column to a column from another tab so that the data from the second column receives the data from the first automatically as per the data feed. If your input data contains the "" & SUBSTITUTE(SUBSTITUTE(TEXTJOIN("ΨΨ",FALSE,YOUR_RANGE),"")&"","//B"),"ЉЉ","" & TEXTJOIN("",TRUE,A1:A3,B1:B3,C1:C3) & "", "//B")Īs a result you will get a dynamic array with the joined/appended ranges: This one includes empty cells: =IFERROR(FILTERXML("" & TEXTJOIN("",FALSE,YOUR_RANGE) & "", "//B"), "") Simple version that ignores empty cells: =FILTERXML("" & TEXTJOIN("",TRUE,YOUR_RANGES) & "", "//B") Just replace YOUR_RANGES with the ranges or dynamic arrays you wish to join: Here is a simple solution using FILTERXML and TEXTJOIN that can append MULTIPLE RANGES OF ANY SIZE, ARRAY FORMULAS AND REGULAR FORMULAS.