Combining Data from Multiple Sheets

T

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
954
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMN
1Mfg/ItemItemItem TypeManufacturerVendorPart #Fix. QtyLamp QtyUnit Cost ($)Extended CostMargin (%)Unit PriceExtended PriceNotes
2MAXLITE 11W LED PL Lamp [GX24q-MCCT]11W LED PL Lamp [GX24q-MCCT]LampMAXLITEMAXLITE9PLG24QVCS 29 45.00%  
3MAXLITE 2X4 LED Flat Panel [WS-MCCT]2X4 LED Flat Panel [WS-MCCT]FixtureMAXLITEMAXLITEMLFP24G427WCSCR103  45.00%  
4MAXLITE 4ft LED Wrap [23W-MCCT]4ft LED Wrap [23W-MCCT]FixtureMAXLITEMAXLITELSU4U23WCSCR1  45.00%  
5MAXLITE 8ft LED Linear Highbay [65W-MCCT]8ft LED Linear Highbay [65W-MCCT]FixtureMAXLITEMAXLITELS2-8U65WCSCR4  45.00%  
6PHILIPS 5.5W LED Par20 Lamp [3K-90CRI]5.5W LED Par20 Lamp [3K-90CRI]LampPHILIPSFELDMAN5.5PAR20/LED/F40/930/DIM/G/T20 6/1FB 96 45.00%  
7          
8          
9          
Product (BOM)
Cell Formulas
RangeFormula
A2:A6A2=SORT(UNIQUE(FILTER('Line Item (input)'!W3:W2002,('Line Item (input)'!W3:W2002 <>"")*('Line Item (input)'!W3:W2002<>0),FALSE)))
B2:B9B2=IFERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2)),"")
C2:C9C2=IFERROR(XLOOKUP(A2,TblECMType[Fixture/Lamp],TblECMType[ECM Type]),"")
D2:D9D2=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:F9F2=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:G9G2=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:H9H2=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:J9J2=IFERROR((G2*I2)+(H2*I2),"")
K2:K9K2=IF(A2<>"", 'Project Items (input)'!$D$6, "")
L2:L9L2=IFERROR(((I2/(1-K2))-I2)+I2,"")
M2:M9M2=IFERROR((G2*L2)+(H2*L2),"")
Dynamic array formulas.


SHEET 2

v2023.4 - Copy.xlsm
ABCDEFGHIJKLMN
1Mfg/ItemItemItem TypeManufacturerVendorPart #QtyUnit Cost ($)Extended CostMargin (%)Unit PriceExtended PriceNotes
2ROSELLE Roll Off Dumpsters – 20 YardRoll Off Dumpsters – 20 YardDumpsterRoselle20 Yard [N]2$1,000.00$2,000.0045.00%$1,818.18$3,636.36
3UNITEDRENTALS Boom, Articulated, 30-39' Diesel DayBoom, Articulated, 30-39' Diesel DayLiftUnited RentalsBoom/39ft/Day150
4      
5      
6      
7      
8      
Rental (BOM)
Cell Formulas
RangeFormula
A2:A3A2=SORT(UNIQUE(FILTER('Line Item (input)'!AD3:AD2002,('Line Item (input)'!AD3:AD2002 <>"")*('Line Item (input)'!AD3:AD2002<>0),FALSE)))
B2:B8B2=IFERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2)),"")
C2:C8C2=IFERROR(XLOOKUP(A2,Table_Rental[MaskedPartNumber], Table_Rental[Type]),"")
E2:E8E2=IFERROR(XLOOKUP(A2,Table_Rental[MaskedPartNumber], Table_Rental[Brand]),"")
F2:F8F2=IFERROR(XLOOKUP(A2,Table_Rental[MaskedPartNumber], Table_Rental[PartNumber]),"")
G2:G8G2=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)),"")
K2K2=IF(A2<>"", 'Project Items (input)'!$D$6, "")
L2L2=IFERROR(((I2/(1-K2))-I2)+I2,"")
M2M2=IFERROR((G2*L2)+(H2*L2),"")
J2:J8J2=IFERROR((G2*I2)+(H2*I2),"")
Dynamic array formulas.


SHEET 3

v2023.4 - Copy.xlsm
ABCDEFGHIJKLMN
1Mfg/ItemItemItem TypeManufacturerVendorPart #QtyUnit Cost ($)Extended CostMargin (%)Unit PriceExtended PriceNotes
2TBD MC Armored Cable 12/2MC Armored Cable 12/2WireFELDMANMC-12-2-1000350$0.50$175.0035.00%$0.77$269.23
3TBD Stranded Copper THHN Cable 12-AWG BLK/WHT/GRNStranded Copper THHN Cable 12-AWG BLK/WHT/GRNWireFELDMANWIRETHHN12550$512.00$281,600.0035.00%$787.69$433,230.77
4        
5        
6        
7        
8        
9        
10        
Material (BOM)
Cell Formulas
RangeFormula
A2:A3A2=SORT(UNIQUE(FILTER('Line Item (input)'!AB3:AB1950,('Line Item (input)'!AB3:AB1950 <>"")*('Line Item (input)'!AB3:AB1950<>0),FALSE)))
B2:B10B2=IFERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2)),"")
C2:C10C2=IFERROR(XLOOKUP(A2,Table_Materials[MaskedPartNumber], Table_Materials[Type]),"")
F2:F10F2=IFERROR(XLOOKUP(A2,Table_Materials[MaskedPartNumber], Table_Materials[PartNumber]),"")
G2:G10G2=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:J10J2=IFERROR((G2*I2)+(H2*I2),"")
K2:K10K2=IF(A2<>"", 'Project Items (input)'!$D$7, "")
L2:L10L2=IFERROR(((I2/(1-K2))-I2)+I2,"")
M2:M10M2=IFERROR((G2*L2)+(H2*L2),"")
Dynamic array formulas.


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

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

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
You can do that using vstack like
Excel Formula: 
=VSTACK(Sheet1!A2#,Sheet2!A2#,Sheet3!A2#)
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0
is it possible to get all of the columns to stack on top of one another using VSTACK? i was able to get column A but the others are not populating the same.
 
Upvote 0
You said you wanted to stack the dynamic filters which is what the formula does.
As the other columns are just formulae based on col A, why not just copy those formulae over?
 
Upvote 0
Yes i will copy the formulas, however i am running into a problem.

A VLOOKUP across 6 tables. Here is my formula but saying too many arguments:

=IFERROR(VLOOKUP(A2,Table_Fixtures,5,0),VLOOKUP(A2,Table_Lamps,4,0),VLOOKUP(A2,Table_Accessory,4,0),VLOOKUP(A2,Table_Controls,3,0),VLOOKUP(A2,Table_Materials,4,0),VLOOKUP(A2,Table_Rental,4,0))))))))
 
Upvote 0
As this is now a totally different question, it needs a new thread. Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
VSTACK Causing "FALSE"
2 3 4
Replies
35
Views
1K
Fluff
Fluff
T
  • Solved
XLOOKUP Formula working in certain sequence but not in others
Replies
7
Views
321
thewiseguy
T
T
  • Question
SUMIFS or SUMPRODUCT?
Replies
1
Views
232
awoohaw
awoohaw
T
  • Solved
Is it Possible to Combine these 3 Formulas??
Replies
1
Views
259
thewiseguy
T
T
  • Question
UNIQUE FILTER giving me a blank row
Replies
5
Views
244
thewiseguy
T

Forum statistics

Threads
1,215,106
Messages
6,123,124
Members
449,097
Latest member
mlckr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top