data alignent - excel challenge.

hopeful_positive

New Member
Joined
Jan 20, 2018
Messages
6
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 !

 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,120
___________________________________________

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.
 
Last edited:

Forum statistics

Threads
1,148,293
Messages
5,745,921
Members
423,984
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top