Combining Data from Multiple Sheets

[thewiseguy]

Hello all - I have 3 worksheets, all using the SORT + UNIQUE + FILTER function to get unique data from other worksheets.

Each worksheet has a different number of active rows (rows where the SORT + UNIQUE + FILTER function could populate data.

I want to create a 4th worksheet that will combine all of the data from these 3 worksheets in continuos rows (no gaps).

SHEET 1

v2023.4 - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Mfg/Item	Item	Item Type	Manufacturer	Vendor	Part #	Fix. Qty	Lamp Qty	Unit Cost ($)	Extended Cost	Margin (%)	Unit Price	Extended Price	Notes
2	MAXLITE 11W LED PL Lamp [GX24q-MCCT]	11W LED PL Lamp [GX24q-MCCT]	Lamp	MAXLITE	MAXLITE	9PLG24QVCS		29			45.00%
3	MAXLITE 2X4 LED Flat Panel [WS-MCCT]	2X4 LED Flat Panel [WS-MCCT]	Fixture	MAXLITE	MAXLITE	MLFP24G427WCSCR	103				45.00%
4	MAXLITE 4ft LED Wrap [23W-MCCT]	4ft LED Wrap [23W-MCCT]	Fixture	MAXLITE	MAXLITE	LSU4U23WCSCR	1				45.00%
5	MAXLITE 8ft LED Linear Highbay [65W-MCCT]	8ft LED Linear Highbay [65W-MCCT]	Fixture	MAXLITE	MAXLITE	LS2-8U65WCSCR	4				45.00%
6	PHILIPS 5.5W LED Par20 Lamp [3K-90CRI]	5.5W LED Par20 Lamp [3K-90CRI]	Lamp	PHILIPS	FELDMAN	5.5PAR20/LED/F40/930/DIM/G/T20 6/1FB		96			45.00%
7
8
9
Product (BOM)

Cell Formulas
Range	Formula
A2:A6	A2	=SORT(UNIQUE(FILTER('Line Item (input)'!W3:W2002,('Line Item (input)'!W3:W2002 <>"")*('Line Item (input)'!W3:W2002<>0),FALSE)))
B2:B9	B2	=IFERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2)),"")
C2:C9	C2	=IFERROR(XLOOKUP(A2,TblECMType[Fixture/Lamp],TblECMType[ECM Type]),"")
D2:D9	D2	=IF(C2="Fixture",XLOOKUP(A2,Table_Fixtures[MaskedPartNumber],Table_Fixtures[Brand]),IF(C2="Lamp",XLOOKUP(A2,Table_Lamps[Lamp],Table_Lamps[Brand]), IF(C2="Accessory",XLOOKUP(A2,Table_Accessory[MaskedPartNumber],Table_Accessory[Brand]),IF(C2="Control",XLOOKUP(A2,Table_Controls[MaskedPartNumber],Table_Controls[Brand],""),""))))
F2:F9	F2	=IF(C2="Fixture",XLOOKUP(A2,Table_Fixtures[MaskedPartNumber],Table_Fixtures[PartNumber],""),IF(C2="Lamp",XLOOKUP(A2,Table_Lamps[Lamp],Table_Lamps[PartNumber],""), IF(C2="Accessory",XLOOKUP(A2,Table_Accessory[MaskedPartNumber],Table_Accessory[PartNumber],""),IF(C2="Control",XLOOKUP(A2,Table_Controls[MaskedPartNumber],Table_Controls[PartNumber],""),""))))
G2:G9	G2	=IF(OR(ISNUMBER(SEARCH("Lamp", A2)), ISNUMBER(SEARCH("tube", A2))), 0,(IF(SUMPRODUCT(--('Line Item (input)'!$W$3:$W$2002=A2),'Line Item (input)'!$Y$3:$Y$2002)=0,"",SUMPRODUCT(--('Line Item (input)'!$W$3:$W$2002=A2),'Line Item (input)'!$Y$3:$Y$2002))))
H2:H9	H2	=IF(OR(ISNUMBER(SEARCH("Lamp", A2)), ISNUMBER(SEARCH("tube", A2))), XLOOKUP(A2,Table_Lamps[Lamp],Table_Lamps[Lamps Per Fixture])*(IF(SUMPRODUCT(--('Line Item (input)'!$W$3:$W$2002=A2),'Line Item (input)'!$Y$3:$Y$2002)=0,"",SUMPRODUCT(--('Line Item (input)'!$W$3:$W$2002=A2),'Line Item (input)'!$Y$3:$Y$2002))),0)
J2:J9	J2	=IFERROR((G2*I2)+(H2*I2),"")
K2:K9	K2	=IF(A2<>"", 'Project Items (input)'!$D$6, "")
L2:L9	L2	=IFERROR(((I2/(1-K2))-I2)+I2,"")
M2:M9	M2	=IFERROR((G2*L2)+(H2*L2),"")
Dynamic array formulas.

SHEET 2

v2023.4 - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Mfg/Item	Item	Item Type	Manufacturer	Vendor	Part #	Qty		Unit Cost ($)	Extended Cost	Margin (%)	Unit Price	Extended Price	Notes
2	ROSELLE Roll Off Dumpsters – 20 Yard	Roll Off Dumpsters – 20 Yard	Dumpster		Roselle	20 Yard [N]	2		$1,000.00	$2,000.00	45.00%	$1,818.18	$3,636.36
3	UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day	Boom, Articulated, 30-39' Diesel Day	Lift		United Rentals	Boom/39ft/Day	15			0
4
5
6
7
8
Rental (BOM)

Cell Formulas
Range	Formula
A2:A3	A2	=SORT(UNIQUE(FILTER('Line Item (input)'!AD3:AD2002,('Line Item (input)'!AD3:AD2002 <>"")*('Line Item (input)'!AD3:AD2002<>0),FALSE)))
B2:B8	B2	=IFERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2)),"")
C2:C8	C2	=IFERROR(XLOOKUP(A2,Table_Rental[MaskedPartNumber], Table_Rental[Type]),"")
E2:E8	E2	=IFERROR(XLOOKUP(A2,Table_Rental[MaskedPartNumber], Table_Rental[Brand]),"")
F2:F8	F2	=IFERROR(XLOOKUP(A2,Table_Rental[MaskedPartNumber], Table_Rental[PartNumber]),"")
G2:G8	G2	=IFERROR(IF(SUMPRODUCT(--('Line Item (input)'!$AD$3:$AD$2002=A2),'Line Item (input)'!$AE$3:$AE$2002)=0,"",SUMPRODUCT(--('Line Item (input)'!$AD$3:$AD$2002=A2),'Line Item (input)'!$AE$3:$AE$2002)),"")
K2	K2	=IF(A2<>"", 'Project Items (input)'!$D$6, "")
L2	L2	=IFERROR(((I2/(1-K2))-I2)+I2,"")
M2	M2	=IFERROR((G2*L2)+(H2*L2),"")
J2:J8	J2	=IFERROR((G2*I2)+(H2*I2),"")
Dynamic array formulas.

SHEET 3

v2023.4 - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Mfg/Item	Item	Item Type	Manufacturer	Vendor	Part #	Qty		Unit Cost ($)	Extended Cost	Margin (%)	Unit Price	Extended Price	Notes
2	TBD MC Armored Cable 12/2	MC Armored Cable 12/2	Wire		FELDMAN	MC-12-2-1000	350		$0.50	$175.00	35.00%	$0.77	$269.23
3	TBD Stranded Copper THHN Cable 12-AWG BLK/WHT/GRN	Stranded Copper THHN Cable 12-AWG BLK/WHT/GRN	Wire		FELDMAN	WIRETHHN12	550		$512.00	$281,600.00	35.00%	$787.69	$433,230.77
4
5
6
7
8
9
10
Material (BOM)

Cell Formulas
Range	Formula
A2:A3	A2	=SORT(UNIQUE(FILTER('Line Item (input)'!AB3:AB1950,('Line Item (input)'!AB3:AB1950 <>"")*('Line Item (input)'!AB3:AB1950<>0),FALSE)))
B2:B10	B2	=IFERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2)),"")
C2:C10	C2	=IFERROR(XLOOKUP(A2,Table_Materials[MaskedPartNumber], Table_Materials[Type]),"")
F2:F10	F2	=IFERROR(XLOOKUP(A2,Table_Materials[MaskedPartNumber], Table_Materials[PartNumber]),"")
G2:G10	G2	=IFERROR(IF(SUMPRODUCT(--('Line Item (input)'!$AB$3:$AB$2002=A2),'Line Item (input)'!$AC$3:$AC$2002)=0,"",SUMPRODUCT(--('Line Item (input)'!$AB$3:$AB$2002=A2),'Line Item (input)'!$AC$3:$AC$2002)),"")
J2:J10	J2	=IFERROR((G2*I2)+(H2*I2),"")
K2:K10	K2	=IF(A2<>"", 'Project Items (input)'!$D$7, "")
L2:L10	L2	=IFERROR(((I2/(1-K2))-I2)+I2,"")
M2:M10	M2	=IFERROR((G2*L2)+(H2*L2),"")
Dynamic array formulas.

SHEET 4 (combination of all 3 sheets, with no gaps)

v2023.4 - Copy.xlsm
	A
1	Mfg/Item
2	MAXLITE 11W LED PL Lamp [GX24q-MCCT]
3	MAXLITE 2X4 LED Flat Panel [WS-MCCT]
4	MAXLITE 4ft LED Wrap [23W-MCCT]
5	MAXLITE 8ft LED Linear Highbay [65W-MCCT]
6	PHILIPS 5.5W LED Par20 Lamp [3K-90CRI]
7	TBD MC Armored Cable 12/2
8	TBD Stranded Copper THHN Cable 12-AWG BLK/WHT/GRN
9	ROSELLE Roll Off Dumpsters – 20 Yard
10	UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day
Sheet1

 

[alansidman]

Did you look at the Power Query solution offered?

 

[thewiseguy]

yes and i think i am confused. for this to work, the data needs to come from tables correct? in my example, the data is coming from worksheets that are not tables.

 

[alansidman]

It will offer you to select Sheets or Tables.

 

[Fluff]

Isn't that only on the Beta channel?

 

[alansidman]

@Fluff
Cannot answer as I am only running Beta but I recall (if my memory serves me) that it may have been on 2019 before I upgraded a couple of years back. I don't really recall but when you combine sheets in a workbook it will automatically convert ranges in the sheets to tables when you select transform.

 

[Fluff]

I think it must just be Beta as I don't get the option to use a range, just a table.

 

[alansidman]

@Fluff
You may be right about Beta, but the data does not need to already formatted as a table as seen in this video. PQ will bring range data into the Editor and can then be mashed up. In any event, I get the sense the OP is not interested in a PQ solution and is happy with the Vstack. And from my perspective it is an opportunity to let people know that there may be more options. As long as they get a solution that works for them, then I am content.
Alan