**Question description:**

*This user has given permission to use the problem statement for this blog.*

We are trying to create a pivot table and combine two separate workbooks, that do not have the same data in the same cells. Is this even possible? For example the data is sales on items by month, one workbook has the sales broke down by month, the other work book has sales broke down by week. We can convert the data, before hand, but we are trying to forecast sales of times.

Solved by T. L. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
09/08/2018 - 10:25

Hi welcome!

User
09/08/2018 - 10:27

Hi there

Excelchat Expert
09/08/2018 - 10:27

You can consolidate sheets in a single pivot, but will need to convert time frames to a consistent format , if expected to use a time reference in the pivot

User
09/08/2018 - 10:29

So it is possible as long as all the data on each Workbook matches exactly

Excelchat Expert
09/08/2018 - 10:29

Not the data necessarily, but the structure (columns and row) format

Excelchat Expert
09/08/2018 - 10:30

Setting up the source data
Each range of data should be arranged in cross-tab format, with matching row and column names for items that you want to summarize together.

User
09/08/2018 - 10:30

Can I send you a copy of the data that we are trying to attempt to do

Excelchat Expert
09/08/2018 - 10:30

yes

User
09/08/2018 - 10:33

This is the data we get from amazon

[Uploaded an Excel file]

User
09/08/2018 - 10:34

This is the data we need to merg into the excel data from amazon

[Uploaded an Excel file]

Excelchat Expert
09/08/2018 - 10:35

Ok, so not different sheets , nut different workbooks

Excelchat Expert
09/08/2018 - 10:35

Let me take a look

User
09/08/2018 - 10:36

You are my hero if you can figure it out , Im on hour 7 of trying to make this work

Excelchat Expert
09/08/2018 - 10:38

have both open, but not folowing the data

Excelchat Expert
09/08/2018 - 10:38

What sheets and columns need to be consolidated?

Excelchat Expert
09/08/2018 - 10:39

I see simple AMAZON forecast by month, what data from LODGE is to be aligned?

User
09/08/2018 - 10:40

The data from Amazon is gathered weekly, and then we need to take that and convert it to lodges monthly forecast form

Excelchat Expert
09/08/2018 - 10:41

Amazon appears to be monthly?

Excelchat Expert
09/08/2018 - 10:41

AUG SEP OCT NOV

Excelchat Expert
09/08/2018 - 10:42

And this data is to be loaded into the TO COMPLETE

Excelchat Expert
09/08/2018 - 10:42

sheet in LODGE?

User
09/08/2018 - 10:43

I'm sorry yes its already converted, but we need to be able to impute the monthly totals into the lodge form automatically each month , with out having to sum up each weeks of sales

User
09/08/2018 - 10:44

all the items need to match the correct item number

Excelchat Expert
09/08/2018 - 10:44

I see the item numbers are unique , so a lookup should work fine

User
09/08/2018 - 10:45

Like a Vlook up ?

User
09/08/2018 - 10:45

another issue is not all item numbers match perfectly there are some on the amazon that are not on the lodge form

Excelchat Expert
09/08/2018 - 10:45

Yes, likley a combo H and V lookup (month and item#) , or INDEX/MATCH

User
09/08/2018 - 10:46

how much would you charge to create a template for us to use and teach us how to use it?

Excelchat Expert
09/08/2018 - 10:46

Sorry, I can't do that here

Excelchat Expert
09/08/2018 - 10:47

This is just a simple formula help site, not solujtion develpoment.

Excelchat Expert
09/08/2018 - 10:47

Do you have an example of the AMAZON sheet before you converted?

Excelchat Expert
09/08/2018 - 10:48

I may be able to help somewhat, but I think a conversion template, that matches the LODGE format, then PIVOT is a way to go, since you may have items in one file and not the other.

User
09/08/2018 - 10:49

let me find the raw data from amazon

Excelchat Expert
09/08/2018 - 10:49

The consolidation and PIVOT is a type of "Outer join" that gets the unique items from both sources

User
09/08/2018 - 10:51

Here is the raw data from amazon

[Uploaded an Excel file]

User
09/08/2018 - 10:51

We only use the P70

Excelchat Expert
09/08/2018 - 10:52

Cell P70?

User
09/08/2018 - 10:53

Only the columns labeled P70

User
09/08/2018 - 10:54

Sorry cell AQ to BP

User
09/08/2018 - 10:56

We have been hand entering all that data by hand

Excelchat Expert
09/08/2018 - 10:56

You mean you added weeks and hand entered into a month column?

User
09/08/2018 - 10:56

yes we do

Excelchat Expert
09/08/2018 - 10:57

yes, we need to help you with that

Excelchat Expert
09/08/2018 - 10:58

What descriptive info is needed?

Excelchat Expert
09/08/2018 - 10:58

ASIN Product Title

Excelchat Expert
09/08/2018 - 10:58

anything else?

User
09/08/2018 - 10:58

model number

User
09/08/2018 - 10:59

these numbers are total units of each item sold

Excelchat Expert
09/08/2018 - 10:59

I dont see model number?

User
09/08/2018 - 11:00

We manual add that part too to each ASIN

Excelchat Expert
09/08/2018 - 11:01

If that is pre-defined , should be able to set that up with a LOOKUP or INDEX/MATch

User
09/08/2018 - 11:01

We have to match them up , since Amazon data comes over with more data than we need, so we first go in and add the model numbers manually then go back and total the months then manually ender line by line into the Lodge form

User
09/08/2018 - 11:03

Here is what is looks like after we added the model number

[Uploaded an Excel file]

Excelchat Expert
09/08/2018 - 11:03

But can every AMAZON ASIN number be pre-defined to a model number? Lookup in a table

User
09/08/2018 - 11:03

yes

Excelchat Expert
09/08/2018 - 11:04

Well I can help with the lookup and will see how best to convert the week data.

Excelchat Expert
09/08/2018 - 11:05

but only 19 minutes left, let me analyze a bit and then give some recommendations.

Excelchat Expert
09/08/2018 - 11:05

Please stand by

Excelchat Expert
09/08/2018 - 11:20

[Uploaded an Excel file]

Excelchat Expert
09/08/2018 - 11:20

We dont have much time . but I set up a simple template design.

Excelchat Expert
09/08/2018 - 11:21

It requires some setup effort , but do it once and wont be much going forward

User
09/08/2018 - 11:21

I really appreciate your help, you are amazing

Excelchat Expert
09/08/2018 - 11:21

I did build in the LOOKUP on this simple, small example

Excelchat Expert
09/08/2018 - 11:21

The setup is to map weeks and create month totals in the template.

Excelchat Expert
09/08/2018 - 11:22

You should know which weeks = month

Excelchat Expert
09/08/2018 - 11:22

make sure you referfence correctly back to the AMAZON source.

Excelchat Expert
09/08/2018 - 11:22

=+'Amazon DATA'!C2, basic + formula to align weeks

Excelchat Expert
09/08/2018 - 11:23

Use the design template as a guide to build out each week to month total.

Excelchat Expert
09/08/2018 - 11:23

This will save you some time

User
09/08/2018 - 11:24

I'm sure I can figure out the vlook up if I had a example

Excelchat Expert
09/08/2018 - 11:24

Please stop back if you need help

User
09/08/2018 - 11:24

Thank you so much

Excelchat Expert
09/08/2018 - 11:24

bye

User
09/08/2018 - 11:24

I will for shore use this service

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*