Hello all,
I am struggling with aligning multiple sets of data with different start dates that I would like to compare so I can see the trends. I have thousands of rows of data, so it not a simple cut/paste to line everything up, I'm hoping to accomplish my goal with a few formulas.
To start simple I have two rows of data. Each with different "start dates". One of Feb-14, the other of Apr-14, with monthly sales of each
For the above table I would like the start date to be period 1 for each row, and go forward from that point. So the first line period 1 would be Feb-14 and second line period 1 would be Apr-14. In other words offset Apr-14 to the left 2 columns.
I have tried to use subtraction of the date across the top row and the start date to assign periods and then use vlookup to replace the values with sales, but that still leaves everything offset further the opposite way. For instance Feb14 minus Feb14 equals 0, and Mar14 minus Feb14 equals 1. In the formula I added +1 to each to make the 0 my period 1. Same with the other row, Apr14 minus Apr14 equals 0, May14 minus Apr14 equals 1. This way I had my start dates to all equal period 1, but I didn't know where to go from there to align all the ones.
This is my end goal:
Please let me know if you have any questions.
Thanks in advance!
I am struggling with aligning multiple sets of data with different start dates that I would like to compare so I can see the trends. I have thousands of rows of data, so it not a simple cut/paste to line everything up, I'm hoping to accomplish my goal with a few formulas.
To start simple I have two rows of data. Each with different "start dates". One of Feb-14, the other of Apr-14, with monthly sales of each
Start Date | Jan-14 | Feb-14 | Mar-14 | Apr-14 | May-14 | Jun-14 | Jul-14 | Aug-14 | Sep-14 | Oct-14 | Nov-14 | Dec-14 |
Feb-14 | 0 | 10 | 10 | 10 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Apr-14 | 0 | 0 | 0 | 15 | 15 | 15 | 0 | 0 | 0 | 0 | 0 | 0 |
For the above table I would like the start date to be period 1 for each row, and go forward from that point. So the first line period 1 would be Feb-14 and second line period 1 would be Apr-14. In other words offset Apr-14 to the left 2 columns.
I have tried to use subtraction of the date across the top row and the start date to assign periods and then use vlookup to replace the values with sales, but that still leaves everything offset further the opposite way. For instance Feb14 minus Feb14 equals 0, and Mar14 minus Feb14 equals 1. In the formula I added +1 to each to make the 0 my period 1. Same with the other row, Apr14 minus Apr14 equals 0, May14 minus Apr14 equals 1. This way I had my start dates to all equal period 1, but I didn't know where to go from there to align all the ones.
This is my end goal:
Period | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
Feb-14 | 10 | 10 | 10 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Apr-14 | 15 | 15 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Please let me know if you have any questions.
Thanks in advance!