Move data in a column to a row

[mukori]

Hello,

I have a set of data in Column A which looks like this:

1
2
3
4
5
6
1
2
3
4
5
6
7
.
.
.

I would like to know if there is a macro where it will poplulate my data in rows and get this result:
1 2 3 4 5 6
1 2 3 4 5 6 7

I need the macro to run through the entire column A.

I have attached a sample spreadsheet.

Thanks,

[BigBas]

In your data, what will trigger the code to move down to the next row? In this example, when the number 1 repeats, it signals a new row. What if two 1's appear immediately after each other? Are there any nuances to consider?

[DGagnon]

is there a qualifer of when the macro would go to the next row? a sample book with data and results could help.

if you want it all in one row, you could simply transpose the data using paste special, and checking the transpose box.

[Pichingualas]

Try this:

Please Login or Register  to view this content.

I hope that helps.

[mukori]

Hello,

Sorry this set of data is nasty. But the start of the column should always says "Start of Log...." and anything else after that will be need to be populated into the columns B, C, D, E and on.

I attached a sample of the actual data.... this may help.

Thanks for all your help.

[mukori]

Hello Pichingualas.

Thanks for your help! Yes it does work, but only half way. I need the macro separate the data so if it starts with 1 it will place it in the next row and then repeat the copying from the column to the row like the numbered example I provided above. Is that possible?

Thanks,

[BigBas]

Hi Mukori,

Take a look at the code posted herein and see if it can work with your dataset. Instead of modifying the data in the same worksheet, it copies it over to a new worksheet. To do so, I made a couple of assumptions. I assumed the data is in a sheet named Sheet1. I also assumed there was a sheet named Sheet2 into which I can copy the data. If this is not the case, then make adjustments to the code or the workbook. Finally, I made an assumption that ALL of the data in Sheet1 is being analyzed, and the first row always starts with the "Start of Log" data. If these aren't true (this matches the format in your sample workbook), please let me know and we can adjust.

Please Login or Register  to view this content.

[mukori]

Awesome! It worked like a charm! Thanks.