Problem with Sections and Index match

[Heltony]

Hi, I have a Input sheet where only the qty changes, there are 6 sections with varying row numbers (between 5 and 50), I can bring the changes to the qty onto the results sheet with index match but it clumps the results together, is there a way of splitting the results up into there sections, I have hopefully provided a sheet showing this.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	This is the input sheet only the Qty changes
2		PRODUCT	PART NUMBER	Quantity	Unit Price				I can get these results with index match
3	Section 1	item 1	111	1	1
4		item 2	222		2			Quote Sheet
5		item 3	333	1	3			PRODUCT	PART NUMBER	Quantity	Unit Price
6								item 1	111	1	1
7	Section 2	Item 1	444		5			item 3	333	1	3
8		Item 2	555	2	6			Item 2	555	2	6
9		Item 3	666		7			Item 3	999	4	11
10
11	Section 3	Item 1	777		9			By Adding the section number and a qty into the product line I can get
12		Item 2	888		10			PRODUCT	PART NUMBER	Quantity	Unit Price
13		Item 3	999	4	11			Section 1		1
14								item 1	111	1	1
15								item 3	333	1	3
16								Section 2		1
17								Item 2	555	2	6
18								Section 3		1
19								Item 3	999	4	11
20
21								Is there anyway I can get
22								PRODUCT	PART NUMBER	Quantity	Unit Price
23								Section 1
24								item 1	111	1	1
25								item 3	333	1	3
26
27								Section 2
28								Item 2	555	2	6
29
30								Section 3
31								Item 3	999	4	11
Sheet1

Cell Formulas
Range	Formula
H6:K9	H6	=IFERROR(INDEX($B$3:$E$13,SMALL(IF((INDEX($B$3:$E$13,,3)>= 1),MATCH(ROW($B$3:$E$13),ROW($B$3:$E$13)),""),ROWS($B$2:B2)),COLUMNS($B$2:B2)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?

23 08 06.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		PRODUCT	PART NUMBER	Quantity	Unit Price			PRODUCT	PART NUMBER	Quantity	Unit Price
3	Section 1	item 1	111	1	1			Section 1
4		item 2	222		2			item 1	111	1	1
5		item 3	333	1	3			item 3	333	1	3
6
7	Section 2	Item 1	444		5			Section 2
8		Item 2	555	2	6			Item 2	555	2	6
9		Item 3	666		7
10								Section 3
11	Section 3	Item 1	777		9			Item 3	999	4	11
12		Item 2	888		10
13		Item 3	999	4	11
14
Sections

Cell Formulas
Range	Formula
H4:K5,H11:K11,H8:K8	H4	=LET(F,MATCH(H3,A:A,0),L,MATCH(TRUE,INDEX(B:B,F):B1000="",0)-1,d,INDEX(B:B,F):INDEX(E:E,F+L),FILTER(d,INDEX(d,0,3)<>"",""))
Dynamic array formulas.

 

[Heltony]

Welcome to the MrExcel board!

Is this what you mean?

23 08 06.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		PRODUCT	PART NUMBER	Quantity	Unit Price			PRODUCT	PART NUMBER	Quantity	Unit Price
3	Section 1	item 1	111	1	1			Section 1
4		item 2	222		2			item 1	111	1	1
5		item 3	333	1	3			item 3	333	1	3
6
7	Section 2	Item 1	444		5			Section 2
8		Item 2	555	2	6			Item 2	555	2	6
9		Item 3	666		7
10								Section 3
11	Section 3	Item 1	777		9			Item 3	999	4	11
12		Item 2	888		10
13		Item 3	999	4	11
14
Sections

Cell Formulas
Range	Formula
H4:K5,H11:K11,H8:K8	H4	=LET(F,MATCH(H3,A:A,0),L,MATCH(TRUE,INDEX(B:B,F):B1000="",0)-1,d,INDEX(B:B,F):INDEX(E:E,F+L),FILTER(d,INDEX(d,0,3)<>"",""))
Dynamic array formulas.

Hi Peter_SSs
Thanks for the prompt reply, your formula works really well to bring the items from the input page but could it bring all the items and section headers without having to identify each section separately on the results page as some of the sections have 50 items or more which could end with the results spilling over,
It's a much better way than my attempt, could I put the section headers above each set of items and bring them across with the items, I could always hide them, I think I'm asking to much for a gap between sections but could your formula bring all the sections and headers, I'm not averse to adding columns if needed, I will try to adapt it myself but I'm pretty sure you stand a much better chance,
thanks Peter_SSs for your help.

 

[Peter_SSs]

Could you remove the merged cells in column A and do it like this?

23 08 06.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		PRODUCT	PART NUMBER	Quantity	Unit Price		SECTION	PRODUCT	PART NUMBER	Quantity	Unit Price
3	Section 1	item 1	111	1	1		Section 1	item 1	111	1	1
4	Section 1	item 2	222		2		Section 1	item 3	333	1	3
5	Section 1	item 3	333	1	3
6							Section 2	Item 2	555	2	6
7	Section 2	Item 1	444		5
8	Section 2	Item 2	555	2	6		Section 3	Item 3	999	4	11
9	Section 2	Item 3	666		7
10
11	Section 3	Item 1	777		9
12	Section 3	Item 2	888		10
13	Section 3	Item 3	999	4	11
14
Sections (2)

Cell Formulas
Range	Formula
G3:K8	G3	=LET(d,A3:INDEX(E:E,LOOKUP("zzz",A1:A1000,SEQUENCE(1000))),z,FILTER(d,(INDEX(d,0,4)<>"")+(INDEX(d,0,1)=""),""),IF(z="","",z))
Dynamic array formulas.

 

[Heltony]

Hi Peter_SSs,
Awesome, that's perfect, I would have never got there on my own, thank you so much.

 

[Peter_SSs]

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