Material Tracking


Please help haha It is difficult to put into words exactly what I am trying to accomplish, but I'll do my best. Please ask questions if I'm not being clear enough. 


I have a material column for material that is being delivered to a job site (Sheet name is Delivery Tracker). Each cell in the column is an individual item. However, identical items will appear multiple times in the column because we get more in stock as the job progresses. In the next column I have the quantities for each delivery of the item. 



On a separate sheet (Let's call it "Onsite Material), I need to track the total of each item so that I can know exactly what I have onsite. However, I can't figure out how create a formula that will list each item, only once.

For example, I have had 3 different deliveries to my project. On all 3 I have received some "#8 Copper Wire", along with other material. 

How do I get my separate sheet ("Onsite Material") to list all of the material, but only list the"#8 Copper Wire" once?

I want to avoid needing to have "#8 Copper Wire" in the formula because it's not feasible to manually input the name of every item into every formula. We have alot of different kinds of material and giving every formula an exact phrase to search for would be far too time consuming.

Essentially I need my "Onsite Material" sheet to recognize all the data in the material column (From the "Delivery Tracker" sheet), populate the individual items from the cells into the "Onsite Material" sheet, and exclude the duplicates so that even if the "#8 Copper Wire" occurs 100 times in the material column, it will only be listed once in the "Onsite Material" sheet. 

After that has been accomplished, I would need something similar to total the quantities of each item. 

Screen Shot 2019-11-05 at 12.43.36 PM.png


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!