Results 1 to 3 of 3

Thread: Moving Data from repetitive rows to repetitive columns

  1. #1

    Moving Data from repetitive rows to repetitive columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have data that I copied from a list on the Internet. When I pasted the data, it loaded all of it into column "A". The rows are

    "Name"
    "Phone Number"
    "City"
    "Contact"
    "Name"
    "Phone Number"
    "City"
    "Contact"
    "Name"
    "Phone Number"
    "City"
    "Contact"

    and so on.....

    I'd like to see those four pieces of information in columns "A"-"D"

    Is there a way to have Excel do the work and load rows 1,5,9,13,17,etc... into column "A", rows 2,6,10,14,18, etc... into column "B" and so on.

    I really appreciate anyone's help on this.

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    If your data in column A on Sheet1
    Set this formula in cell B2 on Sheet2 - Row 1 is header (copy to the right to the column D)
    Code:
    =INDEX(Sheet1!$A$1:$A$100;ROWS($A$1:$A1)*4-3+COLUMNS($A:A)-1)
    Copy all down
    or search for VBA
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Thank You. I couldn't get it to work at first. There was a semicolon instead of a comma after the array. It took me a couple of minutes to find the issue. I plugged it in and now it works.

    Thank you so much. I appreciate the help Navic!!

Tags for this Thread

Posting Permissions

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