thewiseguy
Well-known Member
- Joined
- May 23, 2005
- Messages
- 1,015
- Office Version
- 365
- Platform
- Windows
Hi all. The below worksheet is a summary of items for a specific project. Column A are the unique values of all of the products. I am trying to find the total number of each product in the project (column F) but the products are setup in multiple columns on another sheet.
This is where all of the unnique values and quantities are coming from: Thjis sheet is titled: "Line Item (input)"
I am unsure as to whwere to even start. Any help would be greatly appreciated.
v2023 (version 1).xlsb | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
2 | Mfg/Item | Item | ApplicationType | Manufacturer | Part # | Fix. Qty | ||
3 | EIKO 1L 24W LED 4ft T5 Lamp 5K | 1L 24W LED 4ft T5 Lamp 5K | Linear_Lamp | EIKO | LED24WT5HO/46/850-G9D | |||
4 | ENVISIONLED 8W LED Round Downlight [MCCT] | 8W LED Round Downlight [MCCT] | Downlight | ENVISIONLED | LED-DLJBX-ADJ-3-8W-5CCT-BLK-R | |||
5 | HALCO Joiner Bracket for LWA Wrap | Joiner Bracket for LWA Wrap | Accessory | HALCO | LWA-D/JB | |||
6 | MAXLITE 3L 25W LED 4ft T5 Lamp 5K | 3L 25W LED 4ft T5 Lamp 5K | Linear_Lamp | MAXLITE | L25T5DE450-CG | |||
7 | MAXLITE Emergency battery Backup for Premium Downlights | Emergency battery Backup for Premium Downlights | Accessory | MAXLITE | EBUUP15030N300 | |||
8 | MAXLITE Network Node - Rectangular White | Network Node - Rectangular White | Control | MAXLITE | NN-RTW | |||
9 | TBD 1/2-EMT Conduit 1/2-Inch x 10' | 1/2-EMT Conduit 1/2-Inch x 10' | Materials | TBD | 1/2-EMT | |||
10 | UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day | Boom, Articulated, 30-39' Diesel Day | Rental | UNITED RENTALS | Boom/39ft/Day | |||
Fixtures (BOM) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A3:A10 | A3 | =LET(sh,VSTACK('Line Item (input)'!W3:W502,'Line Item (input)'!Y3:Y502,'Line Item (input)'!AA3:AA502,'Line Item (input)'!AC3:AC502,'Line Item (input)'!AG3:AG502,'Line Item (input)'!AI3:AI502),SORT(UNIQUE(FILTER(sh,(sh <>"")*(sh<>0),"")))) |
B3:B10 | B3 | =IFERROR(RIGHT(A3,LEN(A3)-FIND(" ",A3)),"") |
C3:C10 | C3 | =(XLOOKUP(A3,Tbl_Area[MaskedPartNumber],Tbl_Area[Product_Subgroup],XLOOKUP(A3,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[Product_Subgroup],XLOOKUP(A3,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[Product_Subgroup],XLOOKUP(A3,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[Product_Subgroup],XLOOKUP(A3,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[Product_Subgroup],XLOOKUP(A3,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[Product_Subgroup],XLOOKUP(A3,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[Product_Subgroup],XLOOKUP(A3,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[Product_Subgroup],XLOOKUP(A3,Tbl_Linear[MaskedPartNumber],Tbl_Linear[Product_Subgroup],XLOOKUP(A3,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[Product_Subgroup],XLOOKUP(A3,Tbl_Strip[MaskedPartNumber],Tbl_Strip[Product_Subgroup],XLOOKUP(A3,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[Product_Subgroup],XLOOKUP(A3,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[Product_Subgroup],XLOOKUP(A3,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[Product_Subgroup],XLOOKUP(A3,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[Product_Subgroup],XLOOKUP(A3,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Product_Subgroup],XLOOKUP(A3,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Product_Subgroup],XLOOKUP(A3,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Product_Type],XLOOKUP(A3,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Product_Type])))))))))))))))))))))))))))))) |
D3:D10 | D3 | =(XLOOKUP(A3,Tbl_Area[MaskedPartNumber],Tbl_Area[Brand],XLOOKUP(A3,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[Brand],XLOOKUP(A3,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[Brand],XLOOKUP(A3,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[Brand],XLOOKUP(A3,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[Brand],XLOOKUP(A3,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[Brand],XLOOKUP(A3,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[Brand],XLOOKUP(A3,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[Brand],XLOOKUP(A3,Tbl_Linear[MaskedPartNumber],Tbl_Linear[Brand],XLOOKUP(A3,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[Brand],XLOOKUP(A3,Tbl_Strip[MaskedPartNumber],Tbl_Strip[Brand],XLOOKUP(A3,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[Brand],XLOOKUP(A3,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[Brand],XLOOKUP(A3,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[Brand],XLOOKUP(A3,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[Brand],XLOOKUP(A3,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[Brand],XLOOKUP(A3,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[Brand],XLOOKUP(A3,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[Brand],XLOOKUP(A3,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[Brand],XLOOKUP(A3,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[Brand],XLOOKUP(A3,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[Brand],XLOOKUP(A3,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[Brand],XLOOKUP(A3,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[Brand],XLOOKUP(A3,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[Brand],XLOOKUP(A3,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[Brand],XLOOKUP(A3,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Brand],XLOOKUP(A3,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Brand],XLOOKUP(A3,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Brand],XLOOKUP(A3,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Brand])))))))))))))))))))))))))))))) |
E3:E10 | E3 | =(XLOOKUP(A3,Tbl_Area[MaskedPartNumber],Tbl_Area[PartNumber],XLOOKUP(A3,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[PartNumber],XLOOKUP(A3,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[PartNumber],XLOOKUP(A3,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[PartNumber],XLOOKUP(A3,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[PartNumber],XLOOKUP(A3,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[PartNumber],XLOOKUP(A3,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[PartNumber],XLOOKUP(A3,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[PartNumber],XLOOKUP(A3,Tbl_Linear[MaskedPartNumber],Tbl_Linear[PartNumber],XLOOKUP(A3,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[PartNumber],XLOOKUP(A3,Tbl_Strip[MaskedPartNumber],Tbl_Strip[PartNumber],XLOOKUP(A3,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[PartNumber],XLOOKUP(A3,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[PartNumber],XLOOKUP(A3,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[PartNumber],XLOOKUP(A3,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[PartNumber],XLOOKUP(A3,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[PartNumber],XLOOKUP(A3,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[PartNumber],XLOOKUP(A3,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[PartNumber],XLOOKUP(A3,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[PartNumber],XLOOKUP(A3,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[PartNumber],XLOOKUP(A3,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[PartNumber],XLOOKUP(A3,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[PartNumber],XLOOKUP(A3,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[PartNumber],XLOOKUP(A3,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[PartNumber],XLOOKUP(A3,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[PartNumber],XLOOKUP(A3,Tbl_Materials[MaskedPartNumber],Tbl_Materials[PartNumber],XLOOKUP(A3,Tbl_Rental[MaskedPartNumber],Tbl_Rental[PartNumber],XLOOKUP(A3,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[PartNumber],XLOOKUP(A3,Tbl_Controls[MaskedPartNumber],Tbl_Controls[PartNumber])))))))))))))))))))))))))))))) |
Dynamic array formulas. |
This is where all of the unnique values and quantities are coming from: Thjis sheet is titled: "Line Item (input)"
v2023 (version 1).xlsb | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | |||
2 | Fixture/Lamp | QTY (fixture) | Accessory 1 | QTY (Acc.1) | Accessory 2 | QTY (Acc. 2) | Control | QTY (Control) | Add. Labor | Qty (Add. Labor) | Materials | Qty (Materials) | Rental Equipment | QTY (Rental) | ||
3 | EIKO 1L 24W LED 4ft T5 Lamp 5K | 1 | HALCO Joiner Bracket for LWA Wrap | 1 | MAXLITE Emergency battery Backup for Premium Downlights | 1 | MAXLITE Network Node - Rectangular White | 1 | Cut Tiles | 1 | TBD 1/2-EMT Conduit 1/2-Inch x 10' | 1 | UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day | 1 | ||
4 | MAXLITE 3L 25W LED 4ft T5 Lamp 5K | 10 | HALCO Joiner Bracket for LWA Wrap | 1 | MAXLITE Emergency battery Backup for Premium Downlights | 1 | MAXLITE Network Node - Rectangular White | 1 | Cut Tiles | 1 | TBD 1/2-EMT Conduit 1/2-Inch x 10' | 1 | UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day | 1 | ||
5 | ENVISIONLED 8W LED Round Downlight [MCCT] | 1 | HALCO Joiner Bracket for LWA Wrap | 1 | MAXLITE Emergency battery Backup for Premium Downlights | 1 | MAXLITE Network Node - Rectangular White | 1 | Cut Tiles | 1 | TBD 1/2-EMT Conduit 1/2-Inch x 10' | 1 | UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day | 1 | ||
6 | ENVISIONLED 8W LED Round Downlight [MCCT] | 1 | HALCO Joiner Bracket for LWA Wrap | 1 | MAXLITE Emergency battery Backup for Premium Downlights | 1 | MAXLITE Network Node - Rectangular White | 1 | Cut Tiles | 1 | TBD 1/2-EMT Conduit 1/2-Inch x 10' | 1 | UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day | 1 | ||
7 | ENVISIONLED 8W LED Round Downlight [MCCT] | 1 | HALCO Joiner Bracket for LWA Wrap | 1 | MAXLITE Emergency battery Backup for Premium Downlights | 1 | MAXLITE Network Node - Rectangular White | 1 | Cut Tiles | 1 | TBD 1/2-EMT Conduit 1/2-Inch x 10' | 1 | UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day | 1 | ||
Line Item (input) |
I am unsure as to whwere to even start. Any help would be greatly appreciated.