Search code examples
google-sheetspivot-tablefiltering

Is there a way in Google Sheets to combine JOINs and FILTERs in a dynamic way and with one single formula?



I have a situation like the one depicted in the screenshot.

Screenshot

In column E:E there's a list of words that I want to join.
In column F:F there's a sequence of increasing integers.
In cell G5 theres an integer (1999) that divides the previous increasing list of integers in several groups (1, 2, 3 ,4 etc). The groups show up below 1999, in range G6:G.
In colum H:H I have the desired results, which by now I am able to achieve via a series of JOINS and FILTERS placed manually in each cell under H6. In the first row (cell H6), we find words joined that belong to the group 1, in the cell below words belonging to group 2 and so on.
I would like to achieve the same results but with one single formula, located in cell H6, a formula that would work independently of how long the list of words is, or how large the interger divider is, or how many resulting groups are.
Thank you so much.

Spreadsheet example


Solution

  • Calculating everything directly from your lists of words and integers, and the divisor value (so avoiding the need for columns G & I):

    =arrayformula(let(
    integers,tocol(F6:F,1),
    words,tocol(E6:E,1),
    divisor,$G$5,
    groups,ceiling(integers/divisor,1),
    map(unique(groups),lambda(each,join(", ",filter(words,groups=each))))))