4

I have an excel sheet (mac, Excel 2011) that contains names (1st row), job titles (2nd row), and company/institution (3rd row) in one big column. I am trying to separate them into three different columns. I've tried all the macros i could find. Please help.

So instead of reading like this, as it does now:

Joe Joe
SomeJob
MyCompany

It would read 

JoeJoe    |   SomeJob   |  MyCompany
pnuts
  • 6,242

4 Answers4

2

Try something like this. It is important that the series of 3 stays the same for tyhis to work though.

Sub SingleRow()

Range("A2").Select

Do
    Selection.Cut
        ActiveCell.Offset(-1, 1).Select
        ActiveSheet.Paste
        ActiveCell.Offset(1, -1).Select
        ActiveCell.EntireRow.Select
        ActiveCell.EntireRow.Delete
            ActiveCell.Cut
            ActiveCell.Offset(-1, 2).Select
            ActiveSheet.Paste
            ActiveCell.Offset(1, -2).Select
            ActiveCell.EntireRow.Select
            ActiveCell.EntireRow.Delete
                ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Value = "" And ActiveCell.Offset(-1, 0).Value = ""

End Sub

I tested and this should do the trick.

DesertSpider
  • 21
0

I found this solution the easiest:

https://www.extendoffice.com/documents/excel/3360-excel-transpose-every-5-rows.html

Ensure your single data column is in column A, starting in A1

  1. Insert this formula in a new cell nearby:

    =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))

  2. Just change both occurrences of number "5" to match the size of your group set. (In this case 3)

  3. Drag the formula across to the right to also match the size of your group set. (In this case, drag across 3 columns)

  4. Drag the formula down until your data stops presenting (you'll get "0"'s)

Community
  • 1
Helen
  • 1
0

@Tim Williams's solution is the elegant one but a simple solution is to add a column that repeats 1, 2 and 3. Also copy the data column twice. Delete one cell from the top of the first copy and two from the top of the second copy. Then filter on the column with numbers, deselect 1 and delete what is visible. (Can also then delete the numbers column.)

Process explained in more detail under Question SU495953.

Community
  • 1
pnuts
  • 6,242
-1

What about "Menu Data -> Text In Columns" which separates them all by one separator character you can interactivley choose in the dialog, e.g space