Extract a list from another list

[rline101]

Hello. I have 5 columns of info: ID,Item, Quantity, Units, Category. I'm trying to make a shopping list from all my recipes. So for the first recipe, it would start to look like

ID Item Quantity Units Category
001 carrots 3 cups veges
001 chicken 250 g meat
001 peas, frozen 1 cup veges

etc etc

What I'm trying to do is set up an automatic way to extract any "Items" which match a particular "Category" (say, veges) and list them one cell under another. And also list the "Quantity" for each item in the next column. It would then look like

veges
carrots 3 cups
peas, frozen 1 cup

etc giving a list for each category.

Having arrived here, the new lists will each have many more than one of each item. So if I have 30 recipes, carrots will appear many times in the "veges" list, but often with different quantities. eg. 1 cup, 150g, 3 cups, etc

I then want excel to look at that list and extract each unique item (eg. carrots), and in the next cell to the right, have it list the combined quantities. Eg. 1 cup + 150g + 4 cups etc (in the one cell)

What would be even better is if I separated the measurement from its unit (eg. 3 in one cell and cups in the next) and was then able to list total quantities for each item. Like the following:

veges
carrots 5 cups
600 g
10 pieces
onions 1/2 cup
2 cups
etc

I can get part of the way using filters, but it's not automated. I still have to copy the items each time and I need it to be automated. I figure it might be some sort of complicated conditional lookup function, but my searching on excel help has not been fruitful.

Can anyone get me started (or even finished!) I can email a zipped excel file on request.

Many thanks.[/list]

 

[Joe Was]

You need to prepare your sheet to act as a searchable Table:

Each Item name must be in it's own cell, the quantity must be in the same row as the Item Name, but in it's own cell. The units must be consistant, so for "Carrots" in A1, "5" in B1 and "Cups" then you can never have any other units for that same Item Name, so once you put Carrots in Cups you cannot also have Carrots in Oz. or tbsp.

The you need to establish how out of all your records [recipes], which ones are part of the menu!

Then you need to run code that copies the Item list for that recipe to a new sheet sorts it by Item and does a SubTotal on Item Neme Break!
Each Item name and its SubTotaled quantity then gets added to a new sheet and printed then the Sort Sheet and the Print Sheet get erased or you can Erase them just before the next Run all by VBA code!

 

[rline101]

Thanks Joe. All of what you wrote I understand up to "Then you need to run code". VBA is not my strong point (actually, I've never used it.)

I was hoping there might be a way to do it perhaps using arrays and conditional lookups or something