data alignent - excel challenge.

[hopeful_positive]

Hello everyone!

Thanks for your help in advance !

I have two data sets from 2 different sources capturing the same information : List of stores capturing 4 different products and their amounts by day and month for a specified 8 months.

I have pivoted the data to get what I need from the two data sets.

Now, I am trying to compare the data obtained from the pivot tables but I need to align them exactly with each other before I do further data processing.

Please see attached image for better understanding and clarity.


I have PV1 and PV2. (Pivot Table 1 and Pivot Table 2 respectively).

Each row of the first column in both tables contain the same store name. example - cell B5 (PV1) contains the first store name and cell M5 (PV2) contains the same name.

Under each store name, there is data by month and date - for both tables.

But because the data capturing source are different, there is variablity in the dates i.e. first source might have missed data capturing on one day and there might be an extra row or a row less when expanding (month) and comparing both the tables.

How do I align them using VBA code or excel formula in such a way that:

1) store by store are aligned next to each other and then
2) month by month are aligned which each other then
3) day by day are aligned with each other

Some days even months are going to be completely missed aligned. I would like the formula or code to have PV2 as my primary source of data (more reliable). For example, in the attached image, Month of August is missing in PV1. I would like the code to detect that it is missing in PV1 based on PV2 and add the month and leave it blank.

I am an Excel beginner.

I will be forever grateful to your help !

Thanks everyone !

 

[sheetspread]

___________________________________________

You cannot attach files on this forum... what you need to do is open an account on DropBox (unless you already have one), post the file there, mark it for sharing and then post a link to that file here so we can download it.

Similarly posting images requires linking to a host site like imgur. The MrExcel HTML maker will show pivot tables without any extra formatting.