How do I automatically fill the next available column to my left for longitudinal data?

Select the cells with the data and Hit "F5" and click Special Cells=>Blanks=>ok then Right click on one of the blank cells and select Delete, another window will pop up and select "To Left"

Or use this bit of code

Please Login or Register  to view this content.

Please don't expect us to recreate your worksheet when you can easily attach it to your post.

Rather than going laterally, why not enter vertically which is much easier to calculate with?

You have a lot of cells with the value of " " (Space) inside of them instead of ""(Blank)

I used a filter code to delete these spaces and turn the cell into blanks.
Then use special cells to delete the blanks.
Try the attched macro enabled workbook.
Click Do it button to run the code.
Click reset button and sheet 2 will copy back to sheet1

Someone expert in VBA might be able to write code to convert your horizontal data into a proper vertical arrangement.

If you are just starting out, go vertical to start with. In my own opinion horizontal data is a disaster and worth almost any effort to get rid of.

Originally Posted by faustolopez110

..........how were you able to tell that the spaces were not actually "" (blank)?

.....

I knew something was up when the code from post 2 did not work for you.
When I opened your workbook
I typed in the formula =len(k9) and the answer was 1, when it should have been 0 if it was truly blank.

Your data endpoint is actually GB26 and there are many cells that only contain spaces. A search and replace for spaces produced a count of 3369.