+ Reply to Thread
Results 1 to 5 of 5

Manipulating data

  1. #1
    Registered User
    Join Date
    06-30-2006
    Posts
    6

    Manipulating data

    I have a worksheet containing a large amount of data, with one character per cell, in the following format :-
    1 a x
    2 b y
    3 c z

    I want to manipulate the data to be as follows, again with one character per cell:-
    1 a x 2 b y 3 c z

    I have done this before using 2 different methods, 1 complicated and 1 simple which I stumbled across. Trouble is I can't find either method.
    Any help would be appreciated.
    Kind regards
    duffsparky

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by duffsparky
    I have a worksheet containing a large amount of data, with one character per cell, in the following format :-
    1 a x
    2 b y
    3 c z

    I want to manipulate the data to be as follows, again with one character per cell:-
    1 a x 2 b y 3 c z

    I have done this before using 2 different methods, 1 complicated and 1 simple which I stumbled across. Trouble is I can't find either method.
    Any help would be appreciated.
    Kind regards
    duffsparky
    Hi,

    in D1 put

    =OFFSET($A$1,INT((COLUMN()-1)/3),MOD(COLUMN()-1,3))

    and fill that rightwards as far as required.

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    If your Data, is in lets say column A and it actually looks like A1=1ax A2=2by then you can use this =CONCATENATE(A1,A2,A3) you can keep adding comma's and cells to concatenate.
    Bryans solution works well!
    Not all forums are the same - seek and you shall find

  4. #4
    Registered User
    Join Date
    06-30-2006
    Posts
    6

    Manipulating data

    Hi Brian,
    Thanks for the info.
    I think this is the more complicated way I used before, but many thanks anyway, I'll keep it for next time as I've just finished doing the transpose by cut and paste!

    ------------------------------------------------------------------------
    Hi Simon,
    Thanks for the reply.
    The format is :-
    A1=1 B1=a C1=x
    A2=2 B2=b C2=y
    A3=3 B3=c C3=z

    The format wanted is:-
    A1=1 A2=a A3=x A4=2 A5=b A6=y A7=3 A8=c A9=z

    Kind regards.

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by duffsparky
    Hi Brian,
    Thanks for the info.
    I think this is the more complicated way I used before, but many thanks anyway, I'll keep it for next time as I've just finished doing the transpose by cut and paste!

    ------------------------------------------------------------------------
    Hi Simon,
    Thanks for the reply.
    The format is :-
    A1=1 B1=a C1=x
    A2=2 B2=b C2=y
    A3=3 B3=c C3=z

    The format wanted is:-
    A1=1 A2=a A3=x A4=2 A5=b A6=y A7=3 A8=c A9=z

    Kind regards.
    Hi,

    . . well you did specify one character per cell and then showed the data going left to right, which appears to be along row 1.

    To list down collumn A, in D1 put

    =OFFSET($A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))

    and formula fill that downwards, then Copy column D and Paste Special = Values back over itsself, then delete columns A, B and C

    hth
    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1