excel calculation

Hi All,

I have this spreadsheet which I want to perform some analysis.

so I know that there are 38,695 orders out of which I have 14,064 orders for boilers which are CAT 1.

Now what I want to have is :

show how many orders contained boiler + accessory and if both appear in order were they same supplier.
Dreport.xlsx
ammartahir1978Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
I see you have started to build a Pivot Table with your data and you are filtering to show only Cat 1

With that Pivot if you drag supplier to the Rows pane and place it between "Order Number" and "Product" your Summary will then show if there are multiple suppliers on each order and will show the list of products for each supplier.
0
ammartahir1978Author Commented:
thanks, Rob I tried but it doesn't give me the required results.
0
Rob HensonFinance AnalystCommented:
For example, Order number 5784483 then shows:

5784483
BA/0000020
  100592  Main Eco Elite 25 Combi Boiler
  103849  Baxi/Main/Pott Std Horizontal
GL/0000114
  101303  Heatline 24c Combi Boiler
  101305  Heatline Standard Horizontal
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ammartahir1978Author Commented:
how do i get how many orders are different suppliers
0
ammartahir1978Author Commented:
i want a summary or summarise numbers instead of going through whole spreadsheet
0
Rob HensonFinance AnalystCommented:
Not sure I understand the question.

Are you trying to determine how many suppliers per order. So for the above order that I mentioned the result would be 2 (BA/0000020 and GL/0000114).

Another example, order 7354436 has 3 suppliers (BA/0000020 and CA/0000121 and WO/0000050)

Is that what you require?
0
ammartahir1978Author Commented:
yes please, all I need is the number of orders with different suppliers under one order.
0
Rob HensonFinance AnalystCommented:
See attached.

I have put the pivot back to having the Supplier as a column. Alongside the pivot I have then put a formula checking the total row for each order and counting the number of values under the supplier columns, multiple suppliers will show result of 2 or more.

You can then filter on this column to show the Order Number and number of Suppliers.
Dreport--2-.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ammartahir1978Author Commented:
Excellent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.