How do I create a list with the maximum values for a particular ingredient in differnet recipies.

Hi Azili

If I have understood you correctly you could use an array formula for this:

=MAX(IF(A1:A10="Bean Stew",B1:B10))

This will return the largest value in B1:B10 if the corresponding row in A1:A10 = "Bean Stew"

All arrays MUST be entered with Ctrl+Shift+Enter.
There are also some more important rules with arrays that you can read about on my website, under the link "Array Formulas"

If you will need a few of these formulas I would strongly recommend using the DMAX formula instead. All of the Database formulas are more effecient than arrays in most cases.

Dave

OzGrid Business Applications

Azli, suppose that your data list (in cells A1:B7)
contained:

{"Ingredient","Amount"
;"Milk",1.2
;"Butter",3
;"Suger",3.5
;"Suger",4
;"Milk",5
;"Butter",2}

1. Create a 3rd field labelled "Max" by entering
the array formula, {=MAX((A2=$A$2:$A$7)*$B$2:$B$7)=B2}
into cell C2 and copy down.

2. Next, create a criteria in cells E1:E2 containing
{"Max";TRUE}.
3. Select cell A1 and choose the Data Filter
Advanced AutoFilter... menu command.
4. Choose the "Copy to another location" Action.
5. Enter $A$1:$C$7 as the "List range"
6. Enter $E$1:$E$2 as the "Criteri range"
7. Enter $A$10:$C$10 into the "Copy to" field
8. Check "Unique records only"
9. Press OK.

THANKS!!

How do I generate a report for the different ingredients, choosing only the maximum value of a particular ingredient? Also, need to include different kinds of recipes. Different recipes are on different worksheets. I have tried to record a macro but it takes too long and sometimes hangs.

Example
Cells (A1:C10) on Pie sheet
Apple sugar 5
apple 5
salt 2
Peach sugar 4
peach 4
salt 1
Pumpkin sugar 6
pumpkin 1
salt 0

Cells (A1:C9) on Cake Sheet
CAKE
Chocolate sugar 5
butter 6
flour 7
chocolate 2
Lemon sugar 6
butter 5
flour 4
lemon 3

On column D I did {=MAX(IF(B2:B10=B2,C2:C10))} - array formula.

What I did was to create another sheet what all the possible ingredients (about 1000) and in columns to the right, I make it look on each sheet for the corresponding max in column D.

Then I recorded a macro where I do an Autofilter to take out any ingredients that are used in any recipe and copied the result to another page. The problem is it takes so darned long to do it.

There are lots of other things I have done to this page. Imagine instead of inserting water as an ingredients, I insert "H2O" and then it goes to the ingredient page, looks for what H2O is and inserts "water" in the colum next to it for me. I do this for all ingredients.

I can't figure out how to do this more efficiently and what I have takes TOO long! HELP!!!

p.s. is there anywhay I can create a macro to contain all the ingredients and make it search for water in there if I type H2O??

Mistake! Please read!

It isn't
"Apple sugar" in one cell but "Apple" in A2 and "Sugar" in B2, similiarly for Peach and Pumpkin and the Cakes. SORRY!!!

THANKS!!

How do I generate a report for the different ingredients, choosing only the maximum value of a particular ingredient?
Also, need to include different kinds of recipes. Different recipes are on different worksheets. I have tried to
record a macro but it takes too long and sometimes hangs.

"" means empty.
The first Apple means what type of pie it is. Same goes for the cakes.

Example
Cells (A1:C10) on Pie sheet
"Apple" "sugar" "5"
"" "apple" "5"
"" "salt" "2"
"Peach" "sugar" "4"
"" "peach" "4"
"" "salt" "1"
"Pumpkin" "sugar" "6"
"" "pumpkin" "1"
"" "salt" "0"

Cells (A1:C9) on Cake Sheet
"CAKE"
"Chocolate" "sugar" "5"
"" "butter" "6"
"" "flour" "7"
"" "chocolate" "2"
"Lemon" "sugar" "6"
"" "butter" "5"
"" "flour" "4"
"" "lemon" "3"

On column D I did {=MAX(IF(B2:B10=B2,C2:C10))} - array formula.

What I did was to create another sheet what all the possible ingredients (about 1000) and in columns to the right, I
make it look on each sheet for the corresponding max in column D.

Then I recorded a macro where I do an Autofilter to take out any ingredients that are used in any recipe and copied
the result to another page. The problem is it takes so darned long to do it.

There are lots of other things I have done to this page. Imagine instead of inserting water as an ingredients, I insert
"H2O" and then it goes to the ingredient page, looks for what H2O is and inserts "water" in the colum next to it for
me. I do this for all ingredients.

I can't figure out how to do this more efficiently and what I have takes TOO long! HELP!!!

p.s. is there anyway I can create a macro to contain all the ingredients and make it search for water in there if I
type H2O??