Condition for Torow function

[pto160]

Is it possible to use conditions with the Torow function in conjunction with the choosecolumns function? In this example, the condition is order 300.

Book1
	A	B	C	D	E	F	G
1	Amount	Name	Receipt	Order
2	100	John Smith	a	300
3	200	Jane Joe	b	400
4	200	John Smith	c	300
5
6	Order
7	300	#NAME?	100	b	200	c	200
8
9	What I like
10	300	a	100	c	200
Sheet1

Cell Formulas
Range	Formula
B7	B7	=TOROW(CHOOSECOLS(A2:D4,3,1))

 

[JamesCanale]

MrExcelPlayground18.xlsx
	A	B	C	D	E
1	Amount	Name	Receipt	Order
2	100	John Smith	a	300
3	200	Jane Joe	b	400
4	200	John Smith	c	300
5
6	Order
7	300	a	100	c	200
Sheet17

Cell Formulas
Range	Formula
B7:E7	B7	=TOROW(CHOOSECOLS(FILTER(A2:D4,D2:D4=A7),3,1))
Dynamic array formulas.

 

[pto160]

Thank you. Absolutely brilliant. In the example I have at work the receipt number is a number and the amount I'll format as a number correct to 2 decimal points.
Is there a way to separate each row with a blank cell?

Book1
	A	B	C	D	E	F
9	What I like
10	300	a	100		c	200
Sheet1

 

[JamesCanale]

I'm not sure what you mean by separate each row with a blank cell. It's outputting one row.

 

[JamesCanale]

Do you mean this?

MrExcelPlayground18.xlsx
	A	B	C	D	E	F	G
1	Amount	Name	Receipt	Order
2	100	John Smith	a	300
3	200	Jane Joe	b	400
4	200	John Smith	c	300
5
6	Order
7	300	a	100		c	200
Sheet17

Cell Formulas
Range	Formula
B7:G7	B7	=TOROW(EXPAND(CHOOSECOLS(FILTER(A2:D4,D2:D4=A7),3,1),ROWS(CHOOSECOLS(FILTER(A2:D4,D2:D4=A7),3,1)),3,""))
Dynamic array formulas.

 

[pto160]

Thank you so much. That is exactly what I was looking for. That is a very unique way of using the expand function.
I am just wondering about something. The ,3,"")) at the end of the function.
When would you need to change that number which is currently 3?

 

[Peter_SSs]

Wouldn't this suffice?

23 07 01.xlsm
	A	B	C	D	E	F	G
1	Amount	Name	Receipt	Order
2	100	John Smith	a	300
3	200	Jane Joe	b	400
4	200	John Smith	c	300
5
6	Order
7	300	a	100		c	200
pto160

Cell Formulas
Range	Formula
B7:G7	B7	=TOROW(EXPAND(CHOOSECOLS(FILTER(A2:C4,D2:D4=A7),3,1),,3,""))
Dynamic array formulas.

When would you need to change that number which is currently 3?

If you wanted more than 3 columns in each 'block'. That is, if you wanted more than one blank column between each result. Example - to get 3 blanks between each result.

23 07 01.xlsm
	A	B	C	D	E	F	G	H
1	Amount	Name	Receipt	Order
2	100	John Smith	a	300
3	200	Jane Joe	b	400
4	200	John Smith	c	300
5
6	Order
7	300	a	100				c	200
pto160

Cell Formulas
Range	Formula
B7:K7	B7	=TOROW(EXPAND(CHOOSECOLS(FILTER(A2:C4,D2:D4=A7),3,1),,5,""))
Dynamic array formulas.

 

[pto160]

Peter_SSs, The formula works great. . Thanks for explaining what the 3 at the end means. The expand function is becoming a really useful Excel function.​

Thanks for your help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[pto160]

This is a great formula. I am just wondering if this could be applied to using a sumifs.
I am getting a #N/A for row 7 and 8.

Book1
	A	B	C	D	E	F	G
1	Order	Voucher	Amt
2	100	1001	50
3	100	1001	75
4	200	1002	50
5	100	1003	10
6
7	#NAME?	1001	125		1002	50
8	200	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
9
10	What I like
11	Order	Voucher	Amount		Voucher	Amount
12	100	1001	125		1003	10
13	200	1002	50
Sheet1

Cell Formulas
Range	Formula
A7	A7	=HSTACK(UNIQUE(A2:A5),TOROW(EXPAND(HSTACK(UNIQUE(B2:B5),SUMIFS(C2:C5,B2:B5,UNIQUE(B2:B5))),,3,"")))