Automation

[bakeraj23]

Hi,

This is what I am trying to do in excel.

Say my data is

Date Type Name Stock Sales Code

2004 Fruit Apple 5 -1 220
2004 Fruit Pear 6 3 221
2004 Fruit Grape 1 4 222
2004 Veg Carrot 5 -2 223
2004 Veg Potatoe 5 10 224
2004 Veg Pepper 2 4 225
2005 Fruit Apple 3 3 220
2005 Fruit Pear 2 2 221
2005 Fruit Grape 2 5 222
2005 Veg Carrot 4 -3 223
2005 Veg Potatoe 5 6 224
2005 Veg Pepper 2 2 225
2006 Fruit Apple 4 3 220
2006 Fruit Pear 6 -4 221
2006 Fruit Grape 2 2 222
2006 Veg Carrot 5 1 223
2006 Veg Potatoe 0 5 224
2006 Veg Pepper 2 3 225


My goal is to rank in separate tables, fruits and vegetables by stock value. Negative sales is when the store held stock and ordered more of the product but made no sales.

The table fields are:

Fruits
2006 Stock, Sales,
1
2
3


Vegetables
2006 Stock, Sales,
1
2
3

So, I need to create formulas that account for the first criteria, fruit or vegetable. Second, it needs to rank by stock quantity the names of the fruit (vegetable), and retrieve the necessary fields. I'm stuck because of the double criteria (date and fruit or veg).

Any help is appreciated.

Thank you.

 

[Smitty]

Welcome to the Board!

Have you thought of a Pivot Table?

 

[bakeraj23]

Yes, I am using one right now. The only problem is there are some other calculations I need to do with the data. I want to be able to dump the data in a sheet then have all my fields fill automatically.

This is sort of vague I know.

 

[Smitty]

For the PT, take a look at calculated fields.

Or dump the data into a Table, add your formulas and then put it in a PT.