Excel calculation about bought and sold articles

I have two sheets: (bought.xls and sold.xls )
1> Articles sold and the sales price
2> Articles bought and the buying price
I need the average price of the total bought articles  multiplied by the total sold articles per articlenumber
The best will be if the totals are on the sold.xls so that  i have the total sold per article next to the total bought per article
It was already hard for me to explain in English :) so help is much appreciated  

Thanks Danny
PS I attached from both sheets 2 versions 1 with and 1 without totals per articlecode
Bought-total-per-articlecode.xls
Bought.xls
Sold-total-per-acrticlecode.xls
Sold.xls
LVL 6
Danny KonAsked:
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.

Mike JensenCommented:
I believe that the following site will be of great use to you. It is a much easier process to link two books together. Though, when linking two separate workbooks, it can be a little more complex. I was able to test these steps with success.

https://support.office.com/en-us/article/Connect-data-in-another-workbook-to-your-workbook-3a557ddb-70f3-400b-b48c-0c86ce62b4f5

Hope this helps!
0
Danny KonAuthor Commented:
Mike, thanks but this will not help enough on this specific question

Danny
0
tomfarrarCommented:
Is this what you had in mind?  See red tab on spreadsheet.  I am having trouble uploading due to size.  I may need to zip it and put in next comment.
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.

tomfarrarCommented:
Attachment for my previous comment.  I  combined the buy and sell files into "All", but was only able to give you months 9-10 as the file would not upload.  But should be enough data to see if this is what you are looking for.  - Tom
Bought-And-Sold---Copy.zip
0
Danny KonAuthor Commented:
Tom

The only thing i can do is smile, YES this is exactly what i was looking for :))Maybe the only thing is that i need the name of the article in your result (even more data:) Can i do something? Because i see it but i don't understand how you do it

Thanks Danny
0
Danny KonAuthor Commented:
Tom,
update
I see that i can just add the name in the pivottable so i only need the rest of the data

Thanks Danny
0
tomfarrarCommented:
Not sure what you mean by:

Maybe the only thing is that i need the name of the article in your result (even more data:)

I couldn't upload the full resort.  I am open to how I might get the data to you, and how I could explain how I did it.  Are you familiar with the pivot table working off the data?  If so, that would be obvious.  The preparation of the data used in the pivot table is the key.  Give me your thoughts.  - Tom
0
tomfarrarCommented:
Here is how I populated the blank spaces below "Artikelcode      Month" columns:

Highlight all the data underneath the two column heading.  So you will be highlighting the cells with product and month data and the blank cells.

Use Control G to get to Go To, where you select "Blanks".  This will highlight all the blank cells.

With the blank cells highlighted,  = key and Up Arrow key, then Control/Enter.  This should populate the blank cells with what is above the blanks.  (Hope this makes sense).

Once populated, then highlight the two columns (Artikelcode      Month), Copy and Paste Value.  Now you should have data for each row.

Then I just added "Purchase" and "Sale" to the two sheets and combined into one list which was used for the pivot table.

Let me know if this doesn't make sense.  - Tom
0
tomfarrarCommented:
Oh, and then I added a column that multiplied the price times the quantity before creating the pivot table, but I think that is pretty obvious.
0
Danny KonAuthor Commented:
Tom,

Forget my comment "Maybe the only thing is that i need the name of the article in your result (even more data:)" because i was not looking good.
I have no experience in pivot tables and i use excel as a standard user. Not because i dont want but maybe for the time is it better/possible to send the file by dropbox or google drive?

Thanks Danny
0
tomfarrarCommented:
Sure, I will first need to find out how Dropbox or Google Drive work.
0
tomfarrarCommented:
HI Danny - Also, I may be able to compress the file (less than 2 Meg compressed), and email it to you.  Can you provide me an email?
0
Danny KonAuthor Commented:
Tom,

I think they are not happy about email but lets try
i use a gmail adres with maildannykon to be a little cryptic :)

Thanks Danny
0
tomfarrarCommented:
Hi Danny - I was able to upload a zipped version of the file here at Experts.  Let me know if you have questions.

Red All Tab - Has combined data and pivot table.  The pivot table simply uses the data in columns A-G for its reporting.  

Bought Tab - Items highlighted in yellow were strange occurrences with the word "Fout" in the pricing column.  When I calculated the value amount (price X quantity) for these items, the formula replaced the error with zero.

Good luck!  - Tom
Bought-And-Sold.zip
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
Danny KonAuthor Commented:
Tom,

Thanks so much for your help

Danny
0
tomfarrarCommented:
Glad to be of help, Danny.
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.