+ Reply to Thread
Results 1 to 7 of 7

Convert Columns to rows with DATA

  1. #1
    Registered User
    Join Date
    08-05-2008
    Location
    Jordan
    Posts
    17

    Convert Columns to rows with DATA

    hi all this is my first topic in here, i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table

    A B C D
    1 9 24 25 26
    2 11 24 434 888

    what i want is to concatenate the data in columns B,C,D etc. to the data in column A each in a separate row so i get the following table:

    A
    1 924
    2 925
    3 926
    4 1124
    5 11434
    6 11888

    so in other words i want to concatenate the data in A1 to the data in b1,c1,d1.....etc and then put the output in one column

    thanks in advance
    Last edited by oldchippy; 05-04-2009 at 09:18 AM.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hello raed_237,

    Welcome to the forum!

    with your data in columns A, B, C & D, try this in F1

    =INDIRECT("A"&ROUNDUP((ROW())/3,0)) auto-fill down the column

    Then in G1

    =INDIRECT("B"&ROUNDUP((ROW())/3,0))

    Then in G2

    =INDIRECT("C"&ROUNDUP((ROW())/3,0))

    Then in G3

    =INDIRECT("D"&ROUNDUP((ROW())/3,0))

    Then select cells G1:G3 and auto-fill down

    Does that work for you?
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Contributor
    Join Date
    01-18-2005
    Location
    Auckland New Zealand
    MS-Off Ver
    Office Professional 2007
    Posts
    295
    Have a look at the attached worksheet for an alternative using a compound formula in column H.

    Name your data range 'data', then copy the formula in H1 to your H1, then copy down.

    If you use a different column you will get an incorrect result

    Regards
    Mike

    PS sorry for the strange workbook title - should be 5 cols to one...
    Attached Files Attached Files
    Last edited by Mikeopolo; 08-05-2008 at 03:59 AM.

  4. #4
    Registered User
    Join Date
    08-05-2008
    Location
    Jordan
    Posts
    17
    thx alot oldchippy...your way didn't work though,
    Mikeopolo...man that is wt i really want..thx but i have a problem, the data i work on is very huge, i am talking about more than 10000 records here, so could you please help me to create a formula or a script.

    thx again guys

  5. #5
    Forum Contributor
    Join Date
    01-18-2005
    Location
    Auckland New Zealand
    MS-Off Ver
    Office Professional 2007
    Posts
    295
    raed:

    With 10,000 rows you will need 4 times, ie 40,000 rows to complete the analysis

    So prepare as follows:

    Name your source range as 'data'
    Swap to my worksheet and copy H1 cell
    Swap back to yours, choose Go To (F5), and enter in H1:H40,000 (or whatever the exact number of rows needs to be).
    Select Edit Paste, to copy the formula in to all those rows.

    It may take a moment to calculate...

    Regards
    Mike

  6. #6
    Registered User
    Join Date
    08-05-2008
    Location
    Jordan
    Posts
    17
    thx alot this is working like a charm mate

  7. #7
    Forum Contributor
    Join Date
    01-18-2005
    Location
    Auckland New Zealand
    MS-Off Ver
    Office Professional 2007
    Posts
    295
    Good to know!

+ 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