Grocery list

**jammerculture** · 08-17-2015, 05:12 PM

I have a workbook that my company uses for ordering, different materials on different sheets. When we order, we fill in quantities on each sheet, often with quantities on more then one sheet. What I want to be able to do is have a "summary" sheet that has the whole order on it. So I need a way for excel to look in each sheet and if there is a value in the quantity column will copy the row over to the summary sheet.

My original thread in the functions forum is here:

http://www.excelforum.com/excel-form...mmary-tab.html

I have attached a simple sample of what I am trying to do.

**YasserKhalil** · 08-17-2015, 05:24 PM

Try this code

Please Login or Register  to view this content.

**AB33** · 08-17-2015, 05:26 PM

If each sheet has a heading in row 1, filter is the fastest code. Try the attached.

**Doc.AElstein** · 08-18-2015, 12:20 PM

.. Hi Jammy Cunlture
. here’s another one, using VBA Arrays...

...The code can be greatly simplified, i just prefer to “open” it out a bit to help remind me and understand what is going on...
....Also sections –
3a) can be done away with if you have an idea what the maximum possible number of rows in the grocery list could ever be.
3c) can be done away altogether with unless you may have anything further down in grocery list (This bit just trims an Array for the Outputted grocery list to the maximum needed before pasting out to the Grocery list. )

. Code does the same consolidating as all codes given.
....
( The Before...)..Takes.these

Using Excel 2007

_Row\^Col	A	B
1	Quantity	Item
2		apples
3	3	oranges
4	1	bannana

Fruit

Using Excel 2007

_Row\^Col	A	B
1	Quantity	Item
2		carrot
3		celery
4	3	tomato

Vegetables

Using Excel 2007

_Row\^Col	A	B
1	Quantity	Item
2		chicken
3	1	steak
4		pork chop

Meat

.........

( The After...)..Returns this

Using Excel 2007

_Row\^Col	A	B
1	Quantity	Item
2	3	oranges
3	1	bannana
4	3	tomato
5	1	steak

Grocery List

............................

Code

Please Login or Register  to view this content.

....
Let us all know please how you get on
Alan

**jammerculture** · 08-19-2015, 03:55 PM

Thanks for getting back to me everyone. To be honest this stuff is WAAAYYYY over my head, so I am going to need a bit of time to take a look at it all. I definitely appreciate the help. If I have some questions I'll message you.

Thanks again