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

 

[Rick Rothstein]

Another formula that should work...

=VSTACK(A1:D1,DROP(REDUCE("",UNIQUE(A2:A5),LAMBDA(a,x,LET(f,FILTER(A2:D5,A2:A5=x),VSTACK(a,HSTACK(TAKE(f,1,2),SUM(INDEX(f,,3)),TEXTJOIN(":",,INDEX(f,,4))))))),1))

 

[Rick Rothstein]

=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,"")))))))

In your headers, you listed the first one as "Name" instead of "ID". With that said, you can eliminate that array constant (and shorten your formula) by using the headers that are already in the existing data...

=VSTACK(A1:D1,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,"")))))))

 

[Stephen_IV]

Thanks, Rick! I appreciate all your knowledge, insight, and critique!

 

[pto160]

Rick, thank you so much. The formula works great. This will save me a lot of time.

 

[Rick Rothstein]

Rick, thank you so much. The formula works great. This will save me a lot of time.

Actually, the formula was Stephen_IV's formula... all I did was change one small part of it.

 

[pto160]

Once again thanks Stephen_IV''s for the formula. Still trying to wrap my head around the formula. You can do some magical thing using the MAP function with the LAMDA function.

 

[Rick Rothstein]

Once again thanks Stephen_IV''s for the formula. Still trying to wrap my head around the formula. You can do some magical thing using the MAP function with the LAMDA function.

Have you ever programmed in VBA? If so, MAP (and REDUCE, SCAN, BYROW, BYVAl to some extent) is basically equivalent to a FOR EACH loop... each cell (if MAP's first argument is a range) or element (if it is an array) is fed one at a time into the variable assigned to the LAMBDA which you can then manipulate in the rest of the LAMBDA. MAP is more flexible than a simple FOR loop though in that you can pass multiple ranges, arrays or combinations of them into the LAMBDA (and provide multiple variables inside the LAMBDA to receive them), but for understanding purposes, you do not need to worry about this now. A simple approach that works a lot of the time (but unfortunately not all the time) is to work out the logic (without MAP or LAMBDA) directly on a single cell or value from an array and once you have your formula successfully processing that cell or element, then wrap the formula in a MAP/LAMBDA housing and replace the cell or element with the declared LAMBDA variable. Perhaps approaching it this way will ease your path into using the MAP/LAMBDA functions.

 

[pto160]

Rick, thanks for sharing that knowledge about the MAP/LAMDA functions. I will also look look into the other functions that you referenced as well.

 

[shaowu459]

A single REDUCE also works:

工作簿1.xlsx
	A	B	C	D	E	F	G
1	Name	Amount	Voucher		Name	Amount	Voucher
2	John Smith	50	10020		John Smith	250	10020:10022
3	Paul Rudd	100	10021		Paul Rudd	100	10021
4	John Smith	200	10022		Jane Smith	500	10023
5	Jane Smith	500	10023
Sheet1

Cell Formulas
Range	Formula
E1:G4	E1	=REDUCE(A1:C1,A2:A5,LAMBDA(x,y,IF(OR(y=x),x,VSTACK(x,HSTACK(y,SUMIF(A:A,y,B:B),TEXTJOIN(":",,FILTER(C:C,A:A=y)))))))
Dynamic array formulas.

 

[pto160]

Wow. That reduce function is pretty powerful when combined with lamda. Thanks so much shaowu459. I will use that formula as well.