200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi.
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
Next
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.
=INDIRECT(ADDRESS((ROW()-2)*3+2+COLUMN()-4,1))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?