Create a single list of records

[JannetteChristie]

Hello,

I have the following data on a worksheet and want to create 1 list of the combined columns.

Quoting Tool.xlsm
	A	B	C	D	E	F	G	H	I
1	Config Id	Qty	Description	Cost		Config Id	Qty	Description	Cost
2	1.04	1	Removable Transport bar	0		1	3	Testing	17.52
3	2.03	1	Unit needs to be individually craned	0		2	4	Test line 2	50.24
4	3.04	1	Split ceiling with removable service panel for MVHR	18.3544
5	4.01	2	Soil vent pipe access hatch	14.32652
6	4.03	1	Standard MVHR single condensate pipework incl. tundish	19.21864
7	5.03	1	Scolmore Double Un-Switched Socket	2.578
8	5.04	1	Scolmore Single Un-Switched Socket	1.998
9	5.05	1	Scolmore Switched Fused Spur (Incl. engraving charge)	3.868
10	5.09	4	Scolmore 6-way 2G Minigrid Assembly (no switches included)	8.512
11	5.10	1	Scolmore Minigrid 2-Module Fan Isolator Switch	0
12	6.01	1	1G Empty Drylining Back box for client use	0.62
13				0
14				0
15				0
16				0
Analysis

Cell Formulas
Range	Formula
A2:A16	A2	=IFERROR(INDEX(Configurator!$A$1:$D$1153,SMALL(IF((Configurator!$D$2:$D$1153>0),ROW(Configurator!$D$2:$D$1153)),ROW(1:1)),1),"")
B2:B16	B2	=IFERROR(INDEX(Configurator!$A$1:$D$1153,SMALL(IF((Configurator!$D$2:$D$1153>0),ROW(Configurator!$D$2:$D$1153)),ROW(1:1)),4),"")
C2:C16	C2	=IFERROR(INDEX(Configurator!$A$1:$D$1153,SMALL(IF((Configurator!$D$2:$D$1153>0),ROW(Configurator!$D$2:$D$1153)),ROW(1:1)),2),"")
D2:D16	D2	=IFERROR(SUMIF($K:$K,"="&$A2,$N:$N)*VLOOKUP(A2,Configurator!$A$2:$D$1102,4,FALSE),0)
F2:F16	F2	=IFERROR(INDEX('Non-Standard Costings'!$A$1:$D$1153,SMALL(IF(('Non-Standard Costings'!$D$2:$D$1153>0),ROW('Non-Standard Costings'!$D$2:$D$1153)),ROW(1:1)),1),"")
G2:G16	G2	=IFERROR(INDEX('Non-Standard Costings'!$A$1:$D$1153,SMALL(IF(('Non-Standard Costings'!$D$2:$D$1153>0),ROW('Non-Standard Costings'!$D$2:$D$1153)),ROW(1:1)),4),"")
H2:H16	H2	=IFERROR(INDEX('Non-Standard Costings'!$A$1:$D$1153,SMALL(IF(('Non-Standard Costings'!$D$2:$D$1153>0),ROW('Non-Standard Costings'!$D$2:$D$1153)),ROW(1:1)),2),"")
I2:I16	I2	=IFERROR(INDEX('Non-Standard Costings'!$A$1:$F$1153,SMALL(IF(('Non-Standard Costings'!$D$2:$D$1153>0),ROW('Non-Standard Costings'!$D$2:$D$1153)),ROW(1:1)),6),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
waProducts	=Analysis!$AJ$1	F2:I2, A2:C2

I want to create the following on the same sheet but in columns R and S

Quoting Tool.xlsm
	R	S
1	CQ - Quantity	CQ - Description
2	1	Removable Transport bar
3	1	Unit needs to be individually craned
4	1	Split ceiling with removable service panel for MVHR
5	2	Soil vent pipe access hatch
6	1	Standard MVHR single condensate pipework incl. tundish
7	1	Scolmore Double Un-Switched Socket
8	1	Scolmore Single Un-Switched Socket
9	1	Scolmore Switched Fused Spur (Incl. engraving charge)
10	4	Scolmore 6-way 2G Minigrid Assembly (no switches included)
11	1	Scolmore Minigrid 2-Module Fan Isolator Switch
12	1	1G Empty Drylining Back box for client use
13	3	Testing
14	4	Test line 2
Analysis

Thanks

 

[Peter_SSs]

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’)

Assuming you have Excel 365 you could try this

24 07 09.xlsm
	A	B	C	D	E	F	G	H	I	J							Q	R	S
1	Config Id	Qty	Description	Cost		Config Id	Qty	Description	Cost
2	1.04	1	Removable Transport bar	0		1	3	Testing	17.52									1	Removable Transport bar
3	2.03	1	Unit needs to be individually craned	0		2	4	Test line 2	50.24									1	Unit needs to be individually craned
4	3.04	1	Split ceiling with removable service panel for MVHR	18.3544														1	Split ceiling with removable service panel for MVHR
5	4.01	2	Soil vent pipe access hatch	14.32652														2	Soil vent pipe access hatch
6	4.03	1	Standard MVHR single condensate pipework incl. tundish	19.21864														1	Standard MVHR single condensate pipework incl. tundish
7	5.03	1	Scolmore Double Un-Switched Socket	2.578														1	Scolmore Double Un-Switched Socket
8	5.04	1	Scolmore Single Un-Switched Socket	1.998														1	Scolmore Single Un-Switched Socket
9	5.05	1	Scolmore Switched Fused Spur (Incl. engraving charge)	3.868														1	Scolmore Switched Fused Spur (Incl. engraving charge)
10	5.09	4	Scolmore 6-way 2G Minigrid Assembly (no switches included)	8.512														4	Scolmore 6-way 2G Minigrid Assembly (no switches included)
11	5.1	1	Scolmore Minigrid 2-Module Fan Isolator Switch	0														1	Scolmore Minigrid 2-Module Fan Isolator Switch
12	6.01	1	1G Empty Drylining Back box for client use	0.62														1	1G Empty Drylining Back box for client use
13				0														3	Testing
14				0														4	Test line 2
15				0
16				0
Combine lists

Cell Formulas
Range	Formula
R2:S14	R2	=VSTACK(FILTER(B2:C200,B2:B200<>""),FILTER(G2:H200,G2:G200<>""))
Dynamic array formulas.

 

[JannetteChristie]

Perfect, that works.

 

[Peter_SSs]

Perfect, that works.

Good news. Thanks for letting us know.

It would still help for the future if you updated your details as requested above so that your helpers don't have to guess about what version you have.