google-sheetslambdaspreadsheetflatten

convert two columns containing group and items in group into a single column

I have two columns in a google sheets tab, a group column and an item column. Each entry in the item column is unique and only occurs in one group.

GROUP ITEM
A X
A Y
A Z
B I
B J
B K
C P
C Q
C R

I would like in a new sheet to create a single column that has Group A followed by all items in that group, then an empty row, and then Group B and its items. With this patterned continued for all groups. The formula should automatically update to include any new groups or items that are added in the original sheet.

OUTPUT
A
X
Y
Z
B
I
J
K
C
P
Q
R

I've searched around and haven't found any similar questions on stack overflow or elsewhere. ChatGPT and Gemini provided some suggestions using ARRAYFORMULA for example:

=ARRAYFORMULA(IF(A2:A<>"", A2:A&" | "&TEXTJOIN(", ",TRUE,FILTER(B2:B, A2:A=A2:A)), ""))

but the outputs aren't what I require.

Solution

  • Use Filter and Array Formulas

    You may use FILTER to group the items based on the group code. Afterwards, use a combination of TOCOL and TOROW to transform the array to your desired format. The formula should look like this:

    =LET(a, UNIQUE(TOCOL(A2:A,1)),
     TOCOL(BYROW(a,LAMBDA(x,TOROW({x;filter(B2:B,A2:A=x);" "}))),1))

    Output:
    A
    X
    Y
    Z
    B
    I
    J
    K
    C
    P
    Q
    R

    NOTE: Setting the Group Code (A,B,C in the sample data) to STRONG / BOLD format can only be done manually.

    References: