Query, unique and transpose issues on Gsheets

Question

I have an issue I cannot figure out how to fix. I have 2 columns, one with names and one with homework done.

The data are coming from a query and updating every day from another sheet What I am trying to achive is the following:

Unique names	Homework transposed col B	Homework transposed colC	Homework transposed colD
Person 1	Homework 1	Homework 2	Homework 3
Person 2	Homework 2
Person 3	Homework 2	Homework 3

I tried

=ARRAYFORMULA(TRANSPOSE(FILTER($B$2:$B, $A$2:$A=A2)))

But it gives me the following and it is not an array so I have to pull the formula every time there are new data

Names	Homework transposed col B	Homework transposed colC	Homework transposed colD
Person 1	Homework 1	Homework 2	Homework 3
Person 1	Homework 1	Homework 2	Homework 3
Person 1	Homework 1	Homework 2	Homework 3
Person 2	Homework 2
Person 3	Homework 2	Homework 3
Person 3	Homework 2	Homework 3

Any idea how to fix this?

Harun24hr · Accepted Answer · 2024-08-13 09:45:36Z

0

You may use HSTACK() with MAP() dunction. Try-

=LET(x,UNIQUE(TOCOL(A2:A,1)),HSTACK(x,MAP(x,LAMBDA(z,TOROW(FILTER(B2:B,A2:A=z),1)))))

answered Aug 13, 2024 at 9:45

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1