Spilling Rows in based on another range

[tttommy2]

I have a range (say "XXX") that is generated by the =SORTBY(FILTER()) function. The number of rows in XXX changes depending on the filter criteria, but the number of columns is fixed.

I also have a second range (say- "YYY") with over 100 columns. I want YYY to have the same number of rows as XXX. Specifically, I'd like YYY to always have the same number of rows as XXX, with the first row of YYY populated and then spilling down to match the number of rows in XXX.

Is there an easy way to do this? Basically the number of rows in YYY follows the no of rows in XXX?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Can you post a small sample of what you have & what you want.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[tttommy2]

Thank you Fluff.

I wasn't able to install XL2BB. I fiddled around with Trust settings but of no avail. I have attached a screenshot of a much simplified sheet which pretty much captures my issue.

Basically I want the formulas in the top Row of YYY to spill down to match the number of rows in XXX. These formulas are functions of the equivalent row in XXX.

I'm using Excel 365.

 

[Fluff]

Which cell is the XXX formula in and what cells are YYY?

 

[tttommy2]

Sortbt stuff 1.00.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
7
8										Symbol_Filter	DON						I want the YYY area below to spill down the same no of rows as XXX - (3 Rows for Symbol = "DON")
9
10			Raw Data								XXX						YYY
11			Symbol	Type	Date	Right	Quanitity		Sympol List		Symbol	Type	Date	Right	Quanitity		Date1	B/S
12		1	ASD	VV	20240524	C	4		ASD		DON	VV	20240524	C	250		24-May-24	25000
13		2	ASD	VV	20240524	C	-4		ART		DON	VV	20240524	C	-77
14		3	ASD	VV	20240524	C	26		BER		DON	VV	20240524	C	135
15		4	ART	GG	20240524	C	1500		DES
16		5	BER	GG	20240524	C	6000		DON
17		6	DES	VV	20240524	C	3		FSL
18		7	DES	VV	20240524	C	1045		FNC
19		8	DES	VV	20240524	C	-76		GGM
20		9	DES	VV	20240524	C	-124		GLL
21		10	DON	VV	20240524	C	250		INC
22		11	DON	VV	20240524	C	-77		MCR
23		12	DON	VV	20240524	C	135		MTT
24		13	FSL	VV	20250117	C	300		NVV
25		14	FSL	VV	20260116	C	40		NGG
26		15	FSL	VV	20260116	C	-14		PWW
27		16	FSL	VV	20260116	C	-26		PLL
28		17	FNC	VV	20240524	C	2000		PGG
29		18	GGM	VV	20240531	C	-1		PQQ
30		19	GGM	VV	20240607	C	1		RKK
31		20	GLL	VV	20240531	C	-138		RNN
32		21	GLL	VV	20240531	C	-1		SWW
33		22	GLL	VV	20240531	C	-25		SMM
34		23	GLL	VV	20240531	C	11		VTT
35		24	GLL	VV	20240531	C	25		ZZC
36		25	GLL	VV	20240531	C	10		0
37		26	GLL	VV	20240607	C	26
38		27	GLL	VV	20240607	C	32
Sheet1

Cell Formulas
Range	Formula
I12:I36	I12	=UNIQUE(C12:C129)
K12:O14	K12	=SORTBY(FILTER(C11:G128,((C11:C128=Symbol_Filter)+(C11:C128=uTicker2))*(D11:D128="VV")),FILTER(F11:F128,(C11:C128=Symbol_Filter)*(D11:D128="VV")),1)
Q12	Q12	=TEXT(M13,"0000-00-00")+22/24
R12	R12	=O12*100
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Symbol_Filter	=Sheet1!$K$8	K12

Cells with Data Validation
Cell	Allow	Criteria
K8	List	=$I$12:$I$56

 

[Fluff]

Thanks for that.
How about

=WRAPROWS(TOCOL(IF(SEQUENCE(ROWS(K12#)-1),Q12:R12)),2)

 

[tttommy2]

Thank you for your patience Fluff. I assume you put your Excel formula in Cell Q13? If so, it does spill down correctly but it just repeats the values off "Q12:R12" rather than the formulas.

I simplified my sheet further to help illustrate my point.

Sortbt stuff 1.00.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
10		Raw Data								XXX						YYY
11		Symbol	Type	Date	Right	Quanitity		Sympol List		Symbol	Type	Date	Right	Quanitity		Date1	B/S
12		ASD	VV	20240524	C	4		ASD		DON	VV	20240524	C	250		24-May-24	25000
13		ASD	VV	20240524	C	-4		ART		DON	VV	20240524	C	-77		24-May-24	25000
14		ASD	VV	20240524	C	26		BER		DON	VV	20240531	C	135		24-May-24	25000
15		ART	GG	20240524	C	1500		DES
16		BER	GG	20240524	C	6000		DON
17		DES	VV	20240524	C	3		FSL
18		DES	VV	20240524	C	1045		FNC
19		DES	VV	20240524	C	-76		GGM
20		DES	VV	20240524	C	-124		GLL
21		DON	VV	20240524	C	250		0
22		DON	VV	20240524	C	-77
23		DON	VV	20240531	C	135
24		FSL	VV	20250117	C	300
25		FSL	VV	20260116	C	40
26		FSL	VV	20260116	C	-14
27		FSL	VV	20260116	C	-26
28		FNC	VV	20240524	C	2000
29		GGM	VV	20240531	C	-1
30		GGM	VV	20240607	C	1
31		GLL	VV	20240531	C	-138
32		GLL	VV	20240531	C	-1
33
Sheet1

Cell Formulas
Range	Formula
I12:I21	I12	=UNIQUE(C12:C129)
K12:O14	K12	=SORTBY(FILTER(C11:G128,((C11:C128=Symbol_Filter)+(C11:C128=uTicker2))*(D11:D128="VV")),FILTER(F11:F128,(C11:C128=Symbol_Filter)*(D11:D128="VV")),1)
R12	R12	=O12*100
Q12	Q12	=TEXT(M13,"0000-00-00")+22/24
Q13:R14	Q13	=WRAPROWS(TOCOL(IF(SEQUENCE(ROWS(K12#)-1),Q12:R12)),2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Symbol_Filter	=Sheet1!$K$8	K12

 

[Fluff]

In that case, for Q12

=MAP(INDEX(K12#,,3),LAMBDA(m,TEXT(m,"0000-00-00")+22/24))

and for R12

=MAP(INDEX(K12#,,5),LAMBDA(m,m*100))

 

[tttommy2]

Thank you Fluff. This works exactly as I asked. It may be more complex than I was hoping as I have around 100 columns with many complicated and nested functions. I am going to play around with your solution and see how I can implement it. Many thanks. T

 

[Fluff]

You're welcome & thanks for the feedback.