How do I create a list of items in Excel, using only items which have a quantity assigned to them in another list on a different sheet?

[raybeiler1]

I have a workbook with 4 sheets: Bid Sheet, Assemblies, Material List, and Items. I want to create a list on 'Material List' that contains only items that have a quantity assigned to them on 'Assemblies'. I do not want the quantities to come over as they will be calculated separately. The list of items that I want to check for quantities is in Column E on 'Assemblies'. I want to move the list of items that have quantities to Column B on 'Material List'. I would prefer to do this with a formula, if possible.

 

[Fluff]

Hi & welcome to MrExcel.
Which column on the Assemblies sheet has the quantities?

 

[raybeiler1]

Column C.

 

[Fluff]

Ok, how about

=FILTER(Assemblies!E2:E5000,Assemblies!C2:C5000>0)

 

[raybeiler1]

That returns #SPILL!

 

[Fluff]

In that case clear all cells below where you entered the formula.

 

[raybeiler1]

That worked, but is not exactly what I was looking for. Is there a way to eliminate duplicate items? Also, is there a way to eliminate empty cells/rows?

 

[Fluff]

is there a way to eliminate empty cells/rows?

There shouldn't be any, unless you have quantities in C with no part in E

 

[raybeiler1]

Perfect. That was the problem. But now there are items on there which do not have a quantity in them in Assemblies! Column C. And I also would love to eliminate duplicate items if possible.

 

[Fluff]

Is column C a formula that can return ""