Announcement

Collapse
No announcement yet.

Split Single Column Into Multiple Columns And Rows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Split Single Column Into Multiple Columns And Rows



    Hello all, I have a problem to which I am sure there must be a simple excel formula to solve. I have searched and still not found an answer to my problem. Sorry if the answer comes across as being very fundamental but somehow I have missed it. Here is my situation:

    I have 300 rows worth of data that looks similar to this, all organized in one column:

    John Q. Smith
    Programmer
    2111 NW 13th St
    Anywhereville, USA, 55555
    (555) 555-5555
    Joe P. Snider
    Organizer
    5645 NW 45th St
    Anywhereville, USA, 55555
    (555) 555-5555
    Patty Williams
    Accountant
    6454 NW 34th St
    Anywhereville, USA, 55555
    (555) 555-5555

    As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.

    John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555
    Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555
    Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555

    Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.

    Please help! Thanks! -Steve

  • #2
    Re: Split Single Column Into Multiple Columns And Rows

    This page on transposing with VBA should help

    Transpose Rows Into Columns

    Comment


    • #3
      Re: Split Single Column Into Multiple Columns And Rows

      try

      B1:

      =Indirect(Address((Row(A1)-1)*5+Column(A1),1))

      Copy right + down

      Comment


      • #4
        Re: Split Single Column Into Multiple Columns And Rows

        k.I.S.S I like it

        Comment


        • #5
          Re: Split Single Column Into Multiple Columns And Rows

          Thank you! -Steve

          Comment


          • #6


            Re: Split Single Column Into Multiple Columns And Rows

            hey everyone,

            i have the same problem, but very limited knowledge of excel, so i don't understand jindons help tip.

            what does this mean? ->

            B1:

            =Indirect(Address((Row(A1)-1)*5+Column(A1),1))

            Copy right + down



            Can someone please explain the procedure step by step, for dummies like me?

            Thanks a lot!

            Comment

            Working...
            X