Hstack Problem

[pto160]

I am trying to add an extra column using the Hstack function. The third column I need to use a Text join using the delimiter ":" for the refunds who are duplicate customers and the other ones just the single refund. Here is the data.

Book1
	A	B	C
1	Name	Amount	Voucher
2	John Smith	50	10020
3	Paul Rudd	100	10021
4	John Smith	200	10022
5	Jane Smith	500	10023
6
7	=HSTACK(UNIQUE(A2:A5),SUMIFS(B2:B5,A2:A5,UNIQUE(A2:A5)))
8
9	What I want:
10	Name	Amount	Voucher
11	John Smith	250	10020:10022
12	Paul Rudd	100	10021
13	Jane Smith	500	10023
Sheet1

 

[Stephen_IV]

=LET(u,UNIQUE(A2:A5),v,SUMIF(A2:A5,u,B2:B5),HSTACK(u,v,MAP(u,LAMBDA(x,TEXTJOIN(":",1,IF(A2:A5=x,C2:C5,""))))))

 

[pto160]

Thank you. That works great. Gotta study that MAP and lamda function more.

 

[pto160]

This works great. Thank you so much.
How would you add the column headings to the hstack which is Name, amount and voucher?

 

[Stephen_IV]

One way:

=VSTACK({"Name","Amount","Voucher"},LET(u,UNIQUE(A2:A5),v,SUMIF(A2:A5,u,B2:B5),HSTACK(u,v,MAP(u,LAMBDA(x,TEXTJOIN(":",1,IF(A2:A5=x,C2:C5,"")))))))

 

[pto160]

Absolutely unbelievable. This works fantastic. This will really help me at work.

 

[Stephen_IV]

You are welcome!

 

[pto160]

I am trying to get the unique list by customer ID, but also have the customer name in the list.
Is there a way to do this?

Book1
	A	B	C	D
1	ID	Name	Amount	Voucher
2	0100	John Smith	50	10020
3	0200	Paul Rudd	100	10021
4	0100	John Smith	200	10022
5	0300	Jane Smith	500	10023
6
7
8
9	What I Like
10	ID	Name	Amount	Voucher
11	0100	John Smith	250	10020:10022
12	0200	Paul Rudd	100	10021
13	0300	Jane Smith	500	10023
Sheet1

 

[Stephen_IV]

=VSTACK({"Name","Name","Amount","Voucher"},LET(u,UNIQUE(A2:A5),w,UNIQUE(B2:B5),v,SUMIF(A2:A5,u,C2:C5),HSTACK(u,w,v,MAP(u,LAMBDA(x,TEXTJOIN(":",1,IF(A2:A5=x,D2:D5,"")))))))

 

[pto160]

Absolutely fantastic. Thanks so much, It works great.