+ Reply to Thread
Results 1 to 7 of 7

arranging cells

  1. #1
    Registered User
    Join Date
    05-27-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question arranging cells

    I have copied a heap of store info from the internet but it has all pasted into one row. Each store has five peices of information relating to it (name, city, post code etc). So right now I have about 700 stores and a list that is 3500 rows deep but only 1 column wide. Is it possible to make it so that it is 700 rows deep and 5 colums wide. ie each stores detail occupy one row? Pls excuse my spelling and grammer. English is second langauge..

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: arranging cells

    Hello c,

    welcome to the forum.

    It may be possible to parse the data into columns using "text to columns". Can you upload an Excel file with a few dozen rows of sample data?#

    Click "Go Advanced" below and then the paper clip icon to attach a file. Make it small. A few dozen rows will be enough.

  3. #3
    Registered User
    Join Date
    05-20-2010
    Location
    Fresno, CA
    MS-Off Ver
    2003, 2010
    Posts
    58

    Re: arranging cells

    Try using Text To Columns
    Thanks,
    Ryan



    A word to the wise is infuriating. - Hunter S Thompson

  4. #4
    Registered User
    Join Date
    05-27-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Post Re: arranging cells

    Hey teylyn...thanks for the welcome. I've attached a sample. Thanks for your help.

    c05y
    Attached Files Attached Files

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: arranging cells

    If all addresses have exactly five rows, you can use this in F3

    =INDEX($C:$C,ROW(A1)*5-2+COLUMN(A1)-1)

    copy right to column J and down as far as required.

    Copy the whole new table and use Paste Special - Values to paste only the text.

  6. #6
    Registered User
    Join Date
    05-27-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Thumbs up Re: arranging cells

    Sweet that worked perfectly. If you have the time could you be able to just explain the formula. If not thats fine thanks alot for your help.




    =INDEX($C:$C,ROW(A1)*5-2+COLUMN(A1)-1)

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: arranging cells

    have a look at the Index() function in the help, where you will see that

    =index(A10:A100,4)

    returns A13, which is cell #4 in the range A10:A100.

    In your example, I want this formula in cell F3

    =index(C:C,3) to return the third cell of column C. In G3 want the 4th cell, in H3 the 5th and so on. So I need a formula that will come up with a number from 3 to 7 in the first row, 8 to 12 in the next row and so on.

    Row(A1) = 1
    Column(A1) = 1

    copied to the right, this becomes
    Row(B1) which is 1
    Column(B1) which is 2

    Copied down it becomes
    Row(A2) which is 2
    Column(A2) which is 1

    in cell F3 this formula =ROW(A1)*5-2+COLUMN(A1)-1 translates into
    =1*5-2+1-1 and results in 3. Remember, in F3 I need Index(C:C,3)

    In cell J3 this part of the formula is now ROW(E1)*5-2+COLUMN(E1)-1 and translates into
    =1*5-2+5-1 and results in 7. In J3 I need Index(C:C,7)

    In F4 I need Index(C:C,8) -- and here the formula reads
    =INDEX($C:$C,ROW(A2)*5-2+COLUMN(A2)-1)
    which is =index(C:C,2*5-2+1-1)

    It may take a while to get comfortable with the concept, but using Row() and Column() is a powerful tool to increment numbers across and down.
    Last edited by teylyn; 05-27-2010 at 11:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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