Skip navigation
This discussion is archived

Can Numbers merge columns?

9463 Views 6 Replies Latest reply: Nov 12, 2010 4:39 PM by Barry RSS
Jefferis Peterson Level 2 Level 2 (325 points)
Currently Being Moderated
Nov 12, 2010 12:12 PM
I'm hesitating on the upgrade pricing to Office 2011 and hear Outlook is a disaster. I was waiting for Excel 2011 to get Macros back, but I'm wondering if I can do what I need to do in Numbers instead.
My main Macro that I use is merging columns not cells. IOW, a1 merges with b1, a2 with b2, etc., but not a1 with a2 and b2 and b1.
If there is no macro for this, is there an automator that would do that?
Thanks
2 x 2.8 Ghz Quad-Core, Mac OS X (10.6.5), ATI Radeon HD 2600 XT, 6 gigs RAM
  • Badunit Level 6 Level 6 (10,760 points)
    Currently Being Moderated
    Nov 12, 2010 12:56 PM (in response to Jefferis Peterson)
    I think not. You can merge two side-by-side cells but you can't merge two columns. If you try to merge two columns, they will merge into one cell. You may be able to do what you want in several steps:

    1) Create a new column C =A&" "&B
    2) Select and copy the new column
    3) Paste values to A
    4) Delete columns B and C

    I'm sure an AppleScript could be written to merge the two columns, but there are no automator actions to do it.
    Mac Pro 2008/Intel iMac 2009/Macbook Pro, Mac OS X (10.6.4)
  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 12, 2010 1:10 PM (in response to Jefferis Peterson)
    Jefferis Peterson wrote:
    I'm hesitating on the upgrade pricing to Office 2011 and hear Outlook is a disaster. I was waiting for Excel 2011 to get Macros back, but I'm wondering if I can do what I need to do in Numbers instead.
    My main Macro that I use is merging columns not cells. IOW, a1 merges with b1, a2 with b2, etc., but not a1 with a2 and b2 and b1.
    If there is no macro for this, is there an automator that would do that?
    Thanks


    Hi Jefferis,

    Do you want to merge columns, or to merge the content of the cells in those columns?

    Numbers can merge the content of the cells into a third cell using CONCATENATE (or the & operator), after which the original column pair may be deleted.

    Numbers can merge adjacent cells into a single cell. The content of the two cells (data, or value calculated by a formula) is treated as a pair of text values, separated by a tab character.

    Numbers does not support Macros, but may be scripted using AppleScript.

    Numbers also supports Automator actions.

    Whether either AppleScript or Automator could be used for this is outside my ken, but doesn't appear to be an insurmountable issue.

    It's clear from your description that the desired result is that the cells on each row of the columns be merged while retaining the separation between rows. A clearer description of the result you want in each of those rows (ie. what the newly merged cell will contain) might help whoever takes on the task of buildiing a script or Automator action.

    Regards,
    Barry
  • Badunit Level 6 Level 6 (10,760 points)
    Currently Being Moderated
    Nov 12, 2010 2:27 PM (in response to Jefferis Peterson)
    1) Create or use an empty column, such as column C
    2) In column C put the formula =A&B in every cell in the column.
    3) Select and copy column C
    4) Paste Values to column A
    5) Delete columns B and C
    Mac Pro 2008/Intel iMac 2009/Macbook Pro, Mac OS X (10.6.4)
  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 12, 2010 4:39 PM (in response to Jefferis Peterson)
    Jefferis Peterson wrote:
    NOt sure I understand the code here or what you are doing. Is step 1 a formula of some sort?


    Yes.
    Step 1 in Badunit's first post is a formula (=A&" "&B) that concatenates the contents of the cell in column A, a single space character, and the contents of column B.

    For your sample data, "image/" in column A and "123.jpg" in column B, the result would be "image/ 123.jpg" in the column containing the formula. The letter -only cell reference refers to the cell in the same row as the formula.

    Badunit's second version of the formula (=A&B) eliminates the space between the two values. Result: "image/123.jpg"


    If ALL of the cells in A contain "image/", you can simplify the process slightly.

    In A replace the value "image/" with the formula ="image/"&B.
    Copy the resulting values in A, then
    Edit > Paste Values (still in A)
    Delete column B.

    Regards,
    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.