Brake information from one column into three



I have one column of data with three different types of information. I need a way to sort this information into 3 different columns. Here what I have:

Name 1

City 1

State 1

Name 2

City 2

State 2

Name 3

City 3

State 3

Name 4

City 4

State 4

Name 5

City 5

State 5

Name 6

City 6

State 6

I need each information in one column like this:

Name              City                     State

How do I do that?




Hey there Byron, here is a neat little macro that should do the trick for you:

Sub transpose_n_rows()

xRow = Selection.Rows.Count
nextRow = 1

For i = 1 To xRow Step 3

    Cells(i, 1).Resize(3).Copy
    Range("B1").Offset(nextRow, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    nextRow = nextRow + 1


End Sub

This assumes that the data is in Column A and starts on Row 1 and that you will put it into columns B, C, & D.

Also, select the cells to transpose before you run it.

Let me know if this works!


You may like to try the formula given below in D2 of your worksheet.


Copy it from D2 to E2:F2 and down as far as you have data in column A. Note that the result in columns D:F will have fewer rows than the original data.

You can modify the formula to copy data from a column other than column A. The final "1))" in the formula specifies column A. If your data are elsewhere, just chnage the column number. For example, =INDIRECT(ADDRESS((ROW()-2)*3+2+COLUMN()-4,8)) would copy data from column H, i.e. column 8.

The number 2 appears twice in the formula. In each case it specifies that the first data in the source column are in row 2. You can change the start row but please make sure that you paste the formula in the same row in which the original data start before you copy it down and across.

The "-4" in the formula specifies column D as the first column for the result. You can specify any other column to start off from but make sure that the other two are adjacent.

Finally, the "3" in the formula specifies that each third item will be in the same column or, put differently, that there are 3 result columns. The same formula would work with fewer or more columns.


Why write code, vba, and macros. Isn't   there an add in that will do this stuff?



There's an add on that you just ...
1. highlight the column
2. click an action button
3. Select the cell where you want the 3 columns to start
4. Click OK.

Last week I converted a single column with 40,000 into multiple  columns. Here's the interesting part. The "record" didn't last ha e the same number of rows. They had from 2 to 6 rows per record. Done in 10 minutes. 
gzfraud (rep: 11) Apr 9, '19 at 6:07 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login