google-sheetsformula

How to transpose grouped values next to dates dynamically in Google Sheets?

I have a dataset in Google Sheets where dates appear in Column A and values (like item names) appear in Column B.

The data looks like this:

A B
4/1/2024 Item A
Item B
Item C
4/5/2024 Item D
Item E
4/10/2024 Item F
Item G
Item H
Item I

I want to transpose the item names horizontally next to each date —
meaning, each date will have its corresponding values spread into Column C, D, E, etc.

Expected result:

A B C D E
4/1/2024 Item A Item B Item C
4/5/2024 Item D Item E
4/10/2024 Item F Item G Item H Item I

I have tried using formulas like FILTER, TRANSPOSE, and ARRAYFORMULA,
but I can only get results for the first date or incomplete results.

How can I dynamically populate all the rows like the expected result?

Preferably using a formula, not scripts or manual pivot tables.

I tried but this formula is not accurate

=IF(A2<>"", TRANSPOSE(FILTER(B$2:B, MMULT(N(ROW(A$2:A$1000)<=ROW(A2))*(A$2:A<>""), SEQUENCE(COLUMNS(B$2:B),1,1,0))=1)), "")

Thanks in advance!

Solution

  • You may try:

    =LET(a, A:B,
     b, INDEX(a, , 1),
     c, INDEX(a, , 2),
     d, SCAN("", b, LAMBDA(x, y, IF(y="", x, y))),
     e, UNIQUE(d),
     BYROW(e, LAMBDA(x, HSTACK(x, TOROW(FILTER(c, d=x), 1)))))

    OUTPUT

    A B C D E
    4/1/2024 Item A Item B Item C
    4/5/2024 Item D Item E
    4/10/2024 Item F Item G Item H Item I

    REFERENCES