+ Reply to Thread
Results 1 to 15 of 15

Move every X cells to columns

  1. #1
    Registered User
    Join Date
    07-06-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2016
    Posts
    17

    Move every X cells to columns

    Hi all!

    I am pasting a lot of data into excel from word in, it all comes into the one column in the following format:

    Data1A
    Data1B
    Data1C
    Data1D
    Data2A
    Data2B
    Data2C
    Data2D

    and so on. What I need to do is move it like this:

    Data1A / Data1B / Data1C / Data1D
    Data2A / Data2B / Data2C / Data2D

    and so on. I tried doing a VBA macro but didn't have any luck!

    Thanks in advance!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Move every X cells to columns

    You could use this formula (no VBA needed):

    =IFERROR(IF(COLUMNS($A:A)<=4,INDEX($A$1:$A$20,COLUMNS($A:A)+(ROWS($1:1)-1)*4),""),"")
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Move every X cells to columns

    Just noticed you're in Dublin. Greetings from The North... (although currently in
    France, returning via Rosslare on Sunday).

  4. #4
    Registered User
    Join Date
    07-06-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Move every X cells to columns

    Thanks indeed Glenn! Hope you are enjoying your travels :-)

    When I ran that formula it gave an error and highlighted 4,INDEX ?

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Move every X cells to columns

    Hold on. I'll attach the sheet in a second....

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Move every X cells to columns

    It does/did work OK....

    If you can't make it work, please attach a sample sheet.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    The paperclip icon
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-06-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Move every X cells to columns

    Thanks Glenn, it must have been something silly I was doing.. Your sheet worked perfectly.

    The only thing is that DataxD is a hyperlink and doesn't seem to bring the link over after the formula runs?

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Move every X cells to columns

    Mmmmm. It won't; at least not automatically. give me a mo..

  9. #9
    Registered User
    Join Date
    07-06-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Move every X cells to columns

    Sorry - I should specify it is hyperlinked text

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Move every X cells to columns

    Not convinced that you'll like this, but try:

    =IFERROR(HYPERLINK(IF(COLUMNS($A:A)<=4,INDEX($A$1:$A$20,COLUMNS($A:A)+(ROWS($1:1)-1)*4),"")),"")

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Move every X cells to columns

    Missed your last post. Waddya mean by hyperlinked text??

  12. #12
    Registered User
    Join Date
    07-06-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Move every X cells to columns

    So the dataA-C is text but dataD is text with hyperlink on it (copied from Word)

    like this:
    Data1A
    Data1B
    Data1C
    Data1D

  13. #13
    Registered User
    Join Date
    07-06-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Move every X cells to columns

    I'm not sure if I'm doing something really silly but when I copy the new formula to replace your current one it gives an error as well

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Move every X cells to columns

    It's beer o'clock... look back in the morning. G

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Move every X cells to columns

    Hi... I have edited this post. Here's a better solution. Two formulae, one for the non-hyperlinked cells (same as previous, but only copied over 3 rows, not 4:

    =IFERROR(IF(COLUMNS($A:A)<=4,INDEX($A$1:$A$20,COLUMNS($A:A)+(ROWS($1:1)-1)*4),""),"")

    and one for the external hyperlinks:

    =HYPERLINK("http://"&OFFSET($A$4,(ROWS($1:1)-1)*4,,,))

    File attached.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 10-11-2015 at 04:17 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Move unknown number of headings into cells in new columns
    By LKottal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2015, 05:30 PM
  2. Replies: 2
    Last Post: 10-22-2013, 06:01 AM
  3. Please Help! Macro to move cells from columns to rows?
    By swdawg25 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-25-2012, 06:26 PM
  4. have reference cells move in rows while I move in columns
    By gryffin13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2011, 09:33 AM
  5. Replies: 3
    Last Post: 08-18-2010, 08:00 AM
  6. Move duplicate cells into adjacent columns
    By badgerade in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2009, 07:59 AM
  7. [SOLVED] Prevent changes to cells but let user move columns?
    By sai in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-19-2005, 11:06 AM

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