# 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
###### 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.

Finance 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
Author Commented:
thanks, Rob I tried but it doesn't give me the required results.
0
Finance 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
Author Commented:
how do i get how many orders are different suppliers
0
Author Commented:
i want a summary or summarise numbers instead of going through whole spreadsheet
0
Finance 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
Author Commented:
yes please, all I need is the number of orders with different suppliers under one order.
0
Finance 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

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

Author 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.