Search code examples
google-sheetsmergegoogle-sheets-formulaarray-formulas

Google Sheets Unmatch and Split results

How to combine these two formulas:

Formula 1:

=FILTER(INDIRECT("RangedName1");ISERROR(MATCH(INDIRECT("RangedName1");FILTER(SORT(UNIQUE(flatten(C3:H14));1;1); SORT(UNIQUE(flatten(C3:H14));1;1)<>"");0)))

This first formula, grabs all values from the input range C3:H14 (Columns A to F in this example) to filter duplicates and then the results are flattened and sorted into a single column, this flattened range is then substracted from RangeNamed1 and so the result is a range in a single column of values that do not appear in the range C3:H14.

 Confrontation range         RangedName1    Result
   A       B    .    F            G           H
------------------------------------------------------
Mango   Banana ... Apple      X Apple      Grapes
Banana  Carrot ... Orange     X Banana     Peach
                              X Carrot     .
                              X Orange     .
                                Peach      Watermelon
                              X Mango
                                Grapes
                                .
                                .
                                Watermelon

Formula 2:

=ARRAYFORMULA(IFNA(VLOOKUP(TRANSPOSE(SEQUENCE(ROUNDUP(COUNTA(H3:H15)/4);4;ROW(H3:H15)));{ROW(H3:H15)\H3:H15};2;0)))

This second formula splits the result from Formula 1 (In this example column H) into multiple columns every 4 rows.

And so

                         Split Range
    H           I       J    .    .      M  
-------------------------------------------------
Grapes       Grapes     .             .
Peach        Peach      .             .
.            .          .             .
.            .          .             Watermelon
.
.
.          
Watermelon

When I try to use the Formula 1 into the Formula 2 (Replacing H3:H15) to combine them into a single formula it gives a Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1. error.

How to combine them into a single formula?

Need to be sorted in ascending order only before it is split.

Solution

  • or try like this:

    =ARRAYFORMULA(TRANSPOSE(SPLIT(TRANSPOSE(TRIM(QUERY(QUERY(QUERY(
 {(ROUNDDOWN(SEQUENCE(COUNTUNIQUE(
 FILTER(M2:M; NOT(COUNTIF(FLATTEN(C2:H15); M2:M)))); 1; 0; 1)/A19))+1\QUERY(
 FILTER(M2:M; NOT(COUNTIF(FLATTEN(C2:H15); M2:M))); "where Col1 is not null")}; 
 "select max(Col2) group by Col2 pivot Col1"); "offset 1"; 0);; 9^9))); " ")))

    0