Excel Help for Help Creating A List That Meet Certain Criteria ...

Thanks in advance for any help/suggestions to my question.
Trust also this is the right place for my posting.

I have an excel file containing over 60 thousand rows with entries of Purchase Order’s (PO’s) which is the output of a separate database. Entries are in chronological order and contain, miscellaneous fields such as Amount ($), Article, Article Code, Category, Supplier, Supplier code, etc.

I want to generate a list of PO’s that meet certain criteria (for audit). I can generate separate and intermediate tables and so forth until I get what I want, however, I am somewhat stuck trying to make a more “real time” or simple approach. This is due to the nature of the criteria to be met.

Here is the idea behind the criteria.
· All PO’s over X amount (eg. $10K) should be audited.
· Every n-PO’s should be audited in some categories (eg. Every 5th order of a particular category) and n- varies by category (given in a separate table ie. For article A the sampling frequency is N1, for article B is N2, etc and some articles are not defined)
· Every PO above Y amount in a given category should be audited (Y varies again by category –Y1 for category C, Y2 for category D, etc –also given on a separate table)
· A PO that makes a supplier reach a volume of Z amount should be audited (eg. $100K) and not all suppliers will reach that limit (roughly 10% eventually will).

There is a need to revise some of the criteria (sampling frequency –n, limit amount per category –Y) to keep the number of audits under some limit (say, 1200 per year)

This last one is a "nice to have" but not really so necessary, as I can play manually with the criteria tables (20 or so categories, meaning a total of 40 or so limits -sampling and amount).

Perhaps I am making my life too difficult and someone can help me with a simple approach. Your help is greatly appreciated.

Thanks!

Help creating a list that meet certain criteria ...

Excel Help for Help Creating A List That Meet Certain Criteria ... in Automating Data Analysis Tasks

Help Creating A List That Meet Certain Criteria ...

	Thanks in advance for any help/suggestions to my question. Trust also this is the right place for my posting. I have an excel file containing over 60 thousand rows with entries of Purchase Order’s (PO’s) which is the output of a separate database. Entries are in chronological order and contain, miscellaneous fields such as Amount ($), Article, Article Code, Category, Supplier, Supplier code, etc. I want to generate a list of PO’s that meet certain criteria (for audit). I can generate separate and intermediate tables and so forth until I get what I want, however, I am somewhat stuck trying to make a more “real time” or simple approach. This is due to the nature of the criteria to be met. Here is the idea behind the criteria. · All PO’s over X amount (eg. $10K) should be audited. · Every n-PO’s should be audited in some categories (eg. Every 5th order of a particular category) and n- varies by category (given in a separate table ie. For article A the sampling frequency is N1, for article B is N2, etc and some articles are not defined) · Every PO above Y amount in a given category should be audited (Y varies again by category –Y1 for category C, Y2 for category D, etc –also given on a separate table) · A PO that makes a supplier reach a volume of Z amount should be audited (eg. $100K) and not all suppliers will reach that limit (roughly 10% eventually will). There is a need to revise some of the criteria (sampling frequency –n, limit amount per category –Y) to keep the number of audits under some limit (say, 1200 per year) This last one is a "nice to have" but not really so necessary, as I can play manually with the criteria tables (20 or so categories, meaning a total of 40 or so limits -sampling and amount). Perhaps I am making my life too difficult and someone can help me with a simple approach. Your help is greatly appreciated. Thanks!
	Posted by XcelGirl on 26 Oct 2007

	SOLVED - Thanks! Rate this: (3/5 from 1 vote) Just FYI. Lotus123 from another forum helped me to use the Advance Filtering features to solve this post. You just have to indicate a range for the criteria and then select the appropriate value(s) you need for that criteria. Thanks anyway. Xcel Girl
	Posted by XcelGirl on 01 Nov 2007