How to generate a list of items if quantity greater than 1

[frankheon]

<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--> Hello all,

I'm no expert in excel but I know my way around vba and formulas. However I need your help.

I have a main sheet with a list of 100 items that users may select and choose the quantity for any given item using drop lists.


The data of the main sheet is then transferred to a second sheet where it extracts duplicates and add up all the quantities for any given item. However, this sheet lists all 100 items with their respective quantities in the adjacent cell, many of which have 0 as quantity.

Now what I need is a way to extract all items that have at least 1 quantity and to “populate” a final list which would show only the items that have quantities and their respective quantities.


I could then print or send this final sheet for ordering the requested items that users need.


Can somebody help me how to go about this. Here is an sample of what I mean

Items	qty		result I need	qty
item1	0		item2	2
item2	2		item3	1
item3	1		item6	6
item4	0		item7	2
item5	0
item6	6
item7	2

<tbody>
</tbody>

<tbody>
</tbody>

 

[sheetspread]

filter

 

[frankheon]

I need something that does this automatically. Currently, my macro will print the second sheet with the entire list of items but considering items have grown to 100+, I need to filter out the items with no quantity. but I need this to be done automatically and in real-time.
possible?

 

[sheetspread]

Yes, just record a macro that filters out the zeroes and blanks from the quantity column and add it to your existing code

 

[AhoyNC]

Here is a formula way.
The formula in column D is an array formula and must be entered with CTRL-SHIFT-ENTER.
Copy formulas down as needed.

Excel Workbook
	A	B	C	D	E	F
1	Items	qty		result I need	qty
2	item1	0		item2	2
3	item2	2		item3	1
4	item3	1		item6	6
5	item4	0		item7	2
6	item5	0
7	item6	6
8	item7	2
9
Sheet

 

[frankheon]

unfortunately this does not workout. All I get is D2 = item1 and E2 = 0 the rest is blank when if I copy down the formula onto the other cells. Woruld vba then be easier. I was trying to avoid vba to refrain from using copy&paste within the code

 

[AhoyNC]

Note this is an array formula in D2 and on a PC it must be entered with CTRL-SHIFT-ENTER.
If you just hit enter it will return the wrong answer of item1.

 

[sheetspread]

Did you try the filter?

Excel 2010
	A	B
1	Items	qty
2	item1	0
3	item2	2
4	item3	1
5	item4
6	item5
7	item6	6
8	item7	2
9
10
11
12
13
14	result I need	qty
15	item2	2
16	item3	1
17	item6	6
18	item7	2
Sheet10

Filter out zeroes and blanks:

Excel 2010
	A	B
1	Items	qty
3	item2	2
4	item3	1
7	item6	6
8	item7	2
9
10
11
12
13
14	result I need	qty
15	item2	2
16	item3	1
17	item6	6
18	item7	2
Sheet10

 

[frankheon]

thanks all for your feedback and help.
Even if I wanted to avoid VBA, It was finally the best way to go at it at the end of the day.