Search Column and create one instance + quantity of an item.

[Dishon]

I have a spreadsheet that creates a quote to a client for the install of
equipment in their rooms.
On sheet one I enter the piece of kits model number and using VLOOKUP the
rest of the cells in that row are filled in with the current costs associated
with this item, Make, Price, Discounts etc.
1 NP50 NEC £700 00% £700
1 GKC Unicol £130 10% £117
On Sheet Two I have the quote layout that goes to the client and using SUMIF
the total cost of each room is displayed.
There is often multiple rooms with similar kit and on sheet three I have
shopping list that would go to our purchasing department when we get the job.
This lists all the kit and the quantity. So if we had five rooms with the
above kit we would get,
NP50 NEC SUPPLIER 5 Unit Cost Total Cost
GKC Unicol SUPPLIER 5 Unit Cost Total Cost
However, I have to type in the NP50 in the first cell. Is there a way of
Excel looking down row two of sheet one for the model number and then looking
down row one of sheet three to ensure only one instance of the NP50 exists.

 

[Bob Greenblatt]

I have a spreadsheet that creates a quote to a client for the install of
equipment in their rooms.
On sheet one I enter the piece of kits model number and using VLOOKUP the
rest of the cells in that row are filled in with the current costs associated
with this item, Make, Price, Discounts etc.
1 NP50 NEC £700 00% £700
1 GKC Unicol £130 10% £117
On Sheet Two I have the quote layout that goes to the client and using SUMIF
the total cost of each room is displayed.
There is often multiple rooms with similar kit and on sheet three I have
shopping list that would go to our purchasing department when we get the job.
This lists all the kit and the quantity. So if we had five rooms with the
above kit we would get,
NP50 NEC SUPPLIER 5 Unit Cost Total Cost
GKC Unicol SUPPLIER 5 Unit Cost Total Cost
However, I have to type in the NP50 in the first cell. Is there a way of
Excel looking down row two of sheet one for the model number and then looking
down row one of sheet three to ensure only one instance of the NP50 exists.

I you would lay this out differently, using a "flat database" that showed
the room and items, then you could use advanced filters to get the unique
items and Dxxx formulas to calculate the totals.