1

I have an Excel spreadsheet which is tens of thousands of entries long but it's not organized. All the data is in a single column with each entry taking up 3 rows followed by a space.

For example:

Entry 1 Name
Entry 1 Address
Entry 1 Phone

Entry 2 Name
Entry 2 Address
Entry 2 Phone

This goes on for about half a million entries. I need to organizing it better and would like to transpose it to look like this

Entry 1 Name      Entry 1 Address      Entry 1 Phone
Entry 2 Name      Entry 2 Address      Entry 2 Phone

I've been trying to figure out how to write a formula that I can auto fill in Excel but I can't figure it out. I'm using MS Office on OSX. But any solution be it Excel or non Excel would be great. Thanks!

fixer1234
  • 28,064
davidcondrey
  • 1,748

1 Answers1

0

Sounds like a job for VBA - I believe that this code should work in OSX version of excel but i have only tested it in windows.

Can you run this?

Manually Create a worksheet called Sheet2 and have your data sheet called Sheet1 (or rename in code below). And it starts on Column B on the new Sheet, delete it manually if you don't want it there.

Let me know if it works.

Sub Macro1()

     Application.ScreenUpdating = False  ' Turn off Screen updating

     Dim LastRow As Long                 ' Find the last row of input sheet
     LastRow = Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Row

     Dim row_working_on As Integer

     For row_working_on = 1 To LastRow
          Worksheets("Sheet2").Cells(Int(row_working_on / 4) + 1, row_working_on Mod 4 + 1) = Sheets("Sheet1").Cells(row_working_on, 1).Value
     Next row_working_on

     Application.ScreenUpdating = True  ' turn on Screen updating

End Sub
Brian Folan
  • 426