Converting columns into rows

Microsoft Excel: mac 2008 (mac)
June 28, 2010 at 14:55:47
Specs: Macintosh
OK, I have seen several threads regarding this, but maybe it needs to be made idiot-proof, because I still don't get it. I guess there is a Macros to do this, but some of the instructions do not correspond to what I see in my Excel. Regardless, I have a giant list, three fields of information, Business Name, Address, City. They are all in a single column, no spaces. I want to make them into three columns, obviously. I have figured out that I need two worksheets, a source and a copy. I have figured out that I need to put the the column headers in the first three boxes, then copy them, paste special in the second sheet. From there, I am clueless as to where to put the code I have found, or what I need to adjust within the code. Please help, as this is a massive project that will take forever unless I can figure out a way to batch this. Thanks so much.

See More: Converting columns into rows

June 28, 2010 at 17:58:49
They are all in a single column, no spaces.

Do you mean that the three segments all run together like:


Report •

June 29, 2010 at 04:28:55

For some reason, Microsoft did not include Visual Basic for Applications (VBA) in Excel 2008 for Mac.

As a result the macros that you see, which are written in VBA cannot be used.

You can use AppleScript, but I haven't seen it used on this site.

If this is a one-time rearrangement of the file, you could find someone using Excel on Windows and use the following macro:

Option Explicit

Sub MoveTrans()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim rngDest As Range
Dim intOffst As Integer
Dim intItem As Integer

'set start of range
Set rngStart = Range("A1")
'find end of range
Set rngEnd = Range("A" & CStr(Application.Rows.Count)).End(xlUp)
'set item counter
'-1 as its going to be incremented and first offset will be zero
intItem = -1

'set destination range start
'use row 2 to allow for headers in row 1
Set rngDest = Worksheets("Destination").Range("A2")
'set destination offset counter
intOffst = 0

'loop through source data
For Each rngCell In Worksheets("Source").Range(rngStart, rngEnd)
    'test item counter (3 items per record)
    If intItem = 2 Then
        intItem = 0
        'increment destination row offset
        intOffst = intOffst + 1
        intItem = intItem + 1
    End If
    'move record
    rngCell.Copy _
        Destination:=rngDest.Offset(intOffst, intItem)
Next rngCell
End Sub

This takes this type of data
1	Businessname1
2	Address1
3	City1
and converts it to this:
	A		B		C
1	Businessname1	Address1	City1

An alternative, for a one-time conversion is this:
If all addresses are in column A, starting in row 1
Enter these three formulas:
B1: =A1
C1: =A2
D1: =A3
Select cells B1 to D3
Drag this group of cells to extend them down to the last row of data (the last City address row).
(Don't select B1 to D1 - you must include the empty cells B2 to D3 for this to work).
You will now have all your fields in three columns, but with empty cells in two rows after each record.

Now select Column B
Use the Filter function to filter for Non-blank cells
(I don't have Excel 2008, so I can't give you detailed instructions)
With Non-blank cells selected in column B, select columns B, C and D and copy.
Go to an empty worksheet and select cell A1 and Paste.


Report •

June 29, 2010 at 09:20:58
Thanks so much. The simple changing the values and deleting the blanks was perfect! Thanks so much!!!

Report •
Related Solutions

Ask Question