Combining two Data Sets to make a Report

[mhraja]

Hey,

I have two sets of data

DataSet1:

Materials (along with material's properties) sold per country along with other information like forcast and inventory.

DataSet 2:

Material's inventory and production in each factory.

Report:

I want to make a report which shows materials sold and the inventory and production in each factory.

What I am doing:
VLOOKUP factory's data in Dataset 1 and making a Pivot Table

Problem:
When I VLOOKUP the data, if a material is being sold in more than one country the values for factory comes more than once and when I do PIVOT it becomes multiple.

Part-Solution:
For values of Factory data in PIVOT I displayed Max/Min, that works fine for Material data but when managers need to find data according to Model Type, Model Year or Import then it becomes problem.

Kindly suggest me a way out of this, thanks in advance.

If you want to see the file, please let me know I can send you.

 

[hiker95]

mhraja,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste



If you are not able to give us screenshots:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[mhraja]

Thanks for your message hiker. I am using MS Excel 2007.

Ok thanks for your suggestion about posting screen shots. You can find the screens shots and explaination as follow:

1. Material Data:
Information about the material

2. DataSet1:
The data contain the values for the production and inventory in each factory for the materials:

3. Dataset 2:

The country wise data for the materials, the values from I to P have been VLOOKUPed from DataSet 1 to find the Inventory and Production per plant.

Now I want a report like this one:

Problem:
When I VLOOKUP the data, if a material is being sold in more than one country the values for factory comes more than once and when I do PIVOT it becomes multiple.

Part-Solution:
For values of Factory data in PIVOT I displayed Max/Min, that works fine for Material data but when managers need to find data according to Model Type, Model Year or Import then it becomes problem.

Kindly suggest me a way out of this, thanks in advance.

 

[mhraja]

You can find my file at http://www.box.net/shared/t8avhnb3vx as well.

Thanks (Im in desperate need of help)

 

[hiker95]

mhraja,

The screenshots were graphics, and can not be used on the MrExcel forum.

The box.net workbook is a good start.

I have examined the workbook, and can not come up with a solution for your request.

Please click on the Post Reply button, and then just enter the word BUMP, and click on the Submit Reply button, and someone else will assist you.

 

[mhraja]

Bump

 

[XL&ME]

Bump