Create a list of specific information from data set

[antexity]

Not sure if this is doable, please see attached for a sample of the data.

What I am looking for is an automated way to create a list of items based on the following rules:

For Column J (Short Itm)containing more that one item number
where Column G (QOH) is not null and > 0,
Column H (Max Capacity) not null
Column C = S1
then take the AVG of Column G / AVG Column H.


See "What im looking for" sheet for an example of the output in the borders.

Any help is greatly appreciated, thank you so much,
Jon

sample Item.xlsx

[MarvinP]

Hi antexity,

This looks like an Advanced Filter question to me. The column headings of your data and the columns headings of your criteria need to be spelled exactly the same to start with. See the attached where I've created an Advanced Filter and shown the result in the yellow area. I have not yet done the less than part of the Advanced filter yet.

Read http://www.contextures.com/xladvfilter01.html for more Advanced Filter info and/or examples. Find the attached where I've done some of what I think you want.

[antexity]

Thanks for your reply MarvinP,

Not 100% if this will work. Could be my understanding on Advanced Filters but I noticed you had selected some criteria. In my case, there will be no criteria to go off, or I mean we wont know the specific Item numbers we will be looking for. The only criteria would be the logic to achieve what we want:

"For Column J (Short Itm)containing more that one item number
where Column G (QOH) is not null and > 0,
Column H (Max Capacity) not null
Column C = S1
then take the AVG of Column G / AVG Column H."

Can you set the advance filter to perform a calculation such as (AVG the QOH for the item?) and then the result will be just one row?

Thanks so much
Jon