I need a formula or VBA to copy values to another sheet and omit blanks and zeros

[belalb3]

Hello,
I have a table with 900 maintenance parts. I select the items I want for example 20 items. then I need to go to another sheet and list the items I picked.

In the second sheet, I'm interested in getting the description, value, and quantity only. For now, I'm manually copying the description to the second sheet, and to get the value and quantity, I run a vlookup.

I was trying to find a formula to get the description insted of copying them one by one. I tried if function, but the issue is if-function is pulling the items with "0" in quantity.


I'm interested in getting the description for items that have a quantity greater than 0 and omit the rows with zero/blank quantity

below is an example of the table. Is there a way to do this with formula or VBA?

Product name	Description	Value	Quantity
Car	Item 1	$ 1,000.00	2​
Car	Item 2	$ 500.00	2​
Car	Item 3	$ 250.00	0​
Car	Item 4	$ 100.00	​
Total		$ 1,850.00	4​

I would appreciate if anyone could help me with this!

 

[JB2020]

Hi

Advanced filter should be a good solution for this. The 'list range' will be your data set, for your 'Criteria range' you will need to have a range containing your filter (a cell with "Quantity" and one below with >0). Your 'Copy to' range will be the headings you want, on the second sheet. Hope this helps.

https://support.microsoft.com/en-gb/help/2720580/using-advanced-filters-in-excel-2010

 

[belalb3]

Hi,

I have the code now thanks