Stack horizontal rows vertically automatically

Excel version: 2013
OS: windows 7, on PC

I'm trying to get a certain sheet in this case, "Sheet1", into a certain format/layout that dumps the result in "Sheet2". I basically need the rows for each location ID stacked vertically and the location ID entered for each time/row is stacked. The stacking process is going from right to left. It will make better sense looking at the file. In the attached file, Sheet2 is basically what it should look like. The thing is, I need an equation or macro that's scalable to where it can accommodate hundreds and thousands of rows.

I recorded a macro on a small scale but I'm not good enough to scale it out that large. Any help would save me tremendous amount of time.

This should do it, put this in your module1:

Please Login or Register  to view this content.

Originally Posted by JBeaucaire

This should do it, put this in your module1:

omg, that was quicker than I was expecting. This is what I was looking for. I think I seriously need to learn VBA. I'm not new to programming but never got around to VBA.

JBeaucaire, can you in laymen terms explain to me what some of those sections in the macro are doing? And is there is row limit to this or is the macro accounting for everything in sheet1? Much thanks!

Here's the commented code. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Please Login or Register  to view this content.

Originally Posted by JBeaucaire

Here's the commented code. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

sure, I'll go ahead and mark that. Also, I hate to do this, i shoulda included this initially but I added a revised spreadsheet in my original post. There's 1 more additional thing I was wondering if its possible.
I need 1 more column added on "Sheet2" for Status ID. Basically, on sheet1, columns D-N is status id 3 and columns P-Z is status id 2. If you can do that, it will be perfect.

Hehe, features creeping in...

Please Login or Register  to view this content.

Originally Posted by JBeaucaire

Hehe, features creeping in...

You're a wizard. When I grow up, I wanna be like you. You saved me tons of headaches.

Love to hear it. Pay it forward...

Def, will do!