Transpose repeated, but uneven, sections of data in rows to columns

**central227** · 07-03-2011, 07:44 PM

Currently, I have data in the form of two columns:

A 1
B 2
C 3
D 4
A 5
C 6
D 7
A 8
B 9
D 10
etc, in repeated sections such that the data in the first section (1 2 3 4) are together, the second section (5 6 7) are together and so forth.

I want to convert it into the form of
A B C D
1 2 3 4
5 6 7
8 9 10
Such that the data in each section are put into the same column (e.g. 1,5,8 under column A; 2,9 under column B; 3,6 under column C; 4,7,10 under column D)

Thanks!
central227

**stanleydgromjr** · 07-03-2011, 10:58 PM

central227,

Welcome to the Excel Forum.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, click on the New Post button, then scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

**JBeaucaire** · 07-03-2011, 11:09 PM

'COLUMNS TO ROWS
Here's a macro for merging columns of data to one row matching for column A.

Columns to Rows - Part 2

There's a sample sheet you can test your data in. The only difference is that row 1 needs to "titles", so add those in A1 and B1, then your data in rows 2 down, then run the macro, it does exactly what you've shown.

**central227** · 07-04-2011, 05:08 PM

Originally Posted by JBeaucaire

'COLUMNS TO ROWS
Here's a macro for merging columns of data to one row matching for column A.

Columns to Rows - Part 2

There's a sample sheet you can test your data in. The only difference is that row 1 needs to "titles", so add those in A1 and B1, then your data in rows 2 down, then run the macro, it does exactly what you've shown.

Thanks for the response, but unfortunately that macro didn't do what I need. I've uploaded an example from my data and the desired form to help explain what I'm trying to do.

Actually, the macro you wrote for 'Part 1 - One column with data to row format' is closer to what I want to do. The main differences are that I have data in column B, no spaces between data sections, and my data sections can be either 3 or 4 rows. In particular, the data in column A is what I want to become the column headers and the data in column B what I want to be listed in the rows beneath, but making sure that sections of data (i.e. Name1, Address1, City1, etc.) are in the same row.

**JBeaucaire** · 07-04-2011, 07:23 PM

Yes, your original data does not represent that each string of A-B-C-D are actually related. Much better to post real sample data like you did.

Here you, this will keep the related materials together, with data in columns A:B, run this:

Please Login or Register  to view this content.

**central227** · 07-04-2011, 10:18 PM

Originally Posted by JBeaucaire

Yes, your original data does not represent that each string of A-B-C-D are actually related. Much better to post real sample data like you did.

Here you, this will keep the related materials together, with data in columns A:B, run this:

Please Login or Register  to view this content.

Worked perfectly! Thanks!

**JBeaucaire** · 07-05-2011, 07:18 PM

Please stop using the QUOTE button to quote our entire posts into your responses. It makes your thread very difficult to read. Just use the Quick Reply box below.

Definitely use the QUOTE button when you're discussing something specific someone has said, and be sure to edit the quote down to just the relevant sections.