Sales Report Analysis

**Stopea** · 03-15-2012, 12:19 PM

Hello All,

I have a report (thousands on rows) with sales order data. Each sales order may have numerous sales items, therefore a sales order may appear repeatedly i.e. on more than one row.

I need a quick way to consolidate each sales order to assess if the order had more than one item type.

Ex. order # xxxxx was one just one line, with one item type - i need to determine if it's above a certain order quantity to approve or reject the order.

order # xxxx is 3 lines, with 3 item types, i need to assess that and each item type meets with order quantity to approve or reject the order. it any item in the order fails to meet the order quantity the entire order must be flagged.

any ideas?

thanks

**Kyle123** · 03-15-2012, 12:21 PM

Difficult to visualise without seeing sample data, but couldn't you use a pivot table?

**TMS** · 03-15-2012, 12:27 PM

Like Kyle123 says, a pivot table sounds favourite.

Alternatively, use an advanced filter to extract a list of orders (although this can be done with formulae) then COUNTIF(s), SUMIF(s) and AVERAGEIF(s) to to analyse the order items. You could then filter the output on, say, average item value.

That said, we don't know the criteria for approval so that may not work.

Regards, TMS

**Stopea** · 03-15-2012, 10:12 PM

Thanks for trying to help, i know i wasn't clear.

If you can help, i have attached a file.

What I need to do is:

1 assess if a Sales Order appears more than once in column A.
2. if it does then, depending on the item type, ensure the order meets a requirement, which may be tied weight or amount, depending on the item type.
3. if all the items meet the requirements, then the order may be accepted, otherwise it must be rejected.

I need to quick way to identify orders with multiple items, and ensure they all pass their requirements.

Additionally,

even if the order just appears once, i.e. only one item type per order, what logic may i use in a formula to quickly accept or reject.

examples of requirements are: group 1 orders must exceed a total weight of 1000 lbs. group 2 items must total $1,500

immensely grateful for your help.

Salesanalysis.xlsx

**Kyle123** · 03-16-2012, 05:11 AM

It's a little difficult from your example as each order only appears once, I've adapted it slightly so that some orders occur more than once. HAve a look and see if it helps

**Stopea** · 03-16-2012, 09:23 AM

thanks for your reply kyle123

can suggest logical statements to access for example:

if the order appears just once, based on the item, apply an if then statement to qualify
but if the order appears multiple times, to qualify each line against differing requirements (item one may be based on min. weight, item two on min. amount)

thanks,

**Kyle123** · 03-16-2012, 09:29 AM

Have you had a look at the attachment?

**Stopea** · 03-16-2012, 10:14 AM

I think the solution you created is good, in the PT - on the occassions there are multiple orders the item type is the the same.

how about when the order contains differing item types? i think i can take it from here though -

great idea on the PT set up....thanks,