Creating a list from a master list

Copper Contributor

I have been looking around and can not seem to find the exact thing I need.

So I have a sheet I use to estimate roofing with. I got a list of products with prices and quantities.

My goal is to get a material list created out of the sheet I do all my pricing with. 

 

My main sheet
 Sheet.jpeg

I want the number from the quantity cell and the description cell to transfer over to another sheet on the same workbook. Below is the other sheet on the other workbook.

 

Sheet2.jpeg

On my main list, I want the cell that has a quantity to transfer over to my material list but in the top to bottom order. 

 

Ex: 

The main sheet has:

30 TPO 10'x 100'

40 6' x 100'

20 4' x100

15 1/4" dendeck

15 1/4" dendeck prime 

 

My material list will reflect the quantity of material with the same name on the main sheet.

Sheet3.jpeg

My main list has about 145 entries. 

 

I have been messing with an IF formula but can seem to get it right. I would appreciate some guidance in the right direction.  

 

 

2 Replies
IF you have Excel 365 then you can use FILTER so
=FILTER(Main!A:B, isnumber(Main!A:A), "")
or preferably if the MainTable is formatted as a table then:
=FILTER(MainTable[[Quantity]:{DESCRIPTION]], isnumber(MainTable[Quantity]), "")
if you don't have Excel365 then i suggest using a Pivot Table
Format the Main table as a table and choose insert pivot table
Then you select the source table and destination on the 2nd sheet
Then Select the 2 columns and filter by quantity > 0.
If you have problems, attach a sample and we can show you how.

Thank you, I will start attempting this route. I will update once completed. 

@mtarler