Align CELLS in excel (to the right)

[SysInv]

Hi guys,

I have a gigantic excel sheet based on a timescale, where the dates equal one cell each and where the start date fore the timeseries is different but the end dates are all the same.

I need to "Shift" these cells to the right, so that all end dates are the same. The problem is that I don't know about any easy solution to do this, and to do it manually for 17k rows, well, that's gonna take some time.

I'll try to explain what I need to do. Lets say we have cells that look like this:

3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 9
8 9
8 9
6 7 8 9
9
3 4 5 6 7 8 9

As you can see all rows start with a different "date" but all ends at the same. I would now need to shift the cells that doesn not start with 1 so they at least end with 9. The above shall look something like:

- - 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 9
- - - - - - - - 8 9
- - - - - - - - 8 9
- - - - - - 6 7 8 9
- - - - - - - - - 9
- - -3 4 5 6 7 8 9

(after posting this all spaces was deleted, so I had to use - instead to illustrate my point)

I guess I'll have to create a macro to do it, but I was hoping there was an easier way to do it. Anyone know a good solution?

[Andy Pope]

You would be better off posting a workbook with a before and after view where you manually adjust some of the data.

I guess this is more complicated that simply right aligning the cells?

[martindwilson]

this code should do it

Please Login or Register  to view this content.

[SysInv]

Thank you for your martindwilson,

that almost did it. Th eonly problem is that the values shall not be sorted, since they will contain actual values for each time period, they shall just be shifted to the right to make each cell represent a certain time in the time series.

I really appreciate your help, and it would be great if you just could remove the sorting part of the code so the cells just are shifted to the right and the blanks are thus on the left side.

Thanks,
Johan

[martindwilson]

andy p was right!
try this

Please Login or Register  to view this content.

[SysInv]

Thanks martindwilson,

it worked perfectly. It took all night due to the amount of data, but it did the job!