+ Reply to Thread
Results 1 to 13 of 13

Excel data manipulation

  1. #1
    Registered User
    Join Date
    02-02-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Excel data manipulation

    in a excel column I have data like

    A
    B
    C
    D
    ....
    ...

    I want to convert this data into a row like "A","B","C","D"...

    is it possible ? how ?

    I'm trying to do a quoted transpose basically.

    is there any other easy tool which can do this ?

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel data manipulation

    The easiest way that comes to mind is to highlight the column, then do copy, then put your cursor where you want the data to begin and click on paste special >> transpose.
    is that what you are saying you did but want something easier?
    How many columns wide is the data?

    edit: and if the values you have in your post are the results of formulas transposed, while you have the box open for paste special, click the button for values as well as transpose and you'll be good.
    Last edited by Sam Capricci; 01-17-2019 at 05:52 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    01-11-2019
    Location
    CHI
    MS-Off Ver
    2013
    Posts
    22

    Re: Excel data manipulation

    I agree with the above.

    1. Copy column, then paste special shortcut is Ctrl + Alt + V. Click "e" to select the transpose, and enter for OK.
    2. Then, create another row and type in the formula referencing your transposed values. ="""[cell reference]""" ...after you've done this I suggest turning them into values by again paste special (ctrl + alt + v) and selecting "v" for values.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Excel data manipulation

    An alternative is to employ Power Query/Get and Transform. Here is the MCode for your example. Highlight the range of data. Click on PowerQuery, click on From Range/Table. Highlight the column. Select Transform. Select Home. Close and Load.

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Excel data manipulation

    An alternative is to employ Power Query/Get and Transform.
    The OP has Excel 2007 so I don't this solution will work for him. Power Query doesn't work with XL-2007 does it?

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Excel data manipulation

    You could use a formula, e.g. in B1, if vertical data is in A1 down:

    =INDEX($A:$A,COLUMNS($B:B))

    copy across as required.

    Hope this helps.

    Pete
    Last edited by Pete_UK; 01-17-2019 at 08:58 PM. Reason: anchored column A

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Excel data manipulation

    @GeofffW283
    Good point. I missed that. PQ only works with 2010 and later versions.

  8. #8
    Registered User
    Join Date
    02-02-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Excel data manipulation

    transpose works.

    https://youtu.be/iocgUlUAjdE

    But I am stuck at quoting the values.

    I want to quote the transposed data .

    I want to make "A","B","C","D"

    how to quote transposed values ?

    I'm using excel 2007
    Last edited by zerodegree; 01-18-2019 at 12:17 AM.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Excel data manipulation

    I want to convert this data into a row like "A","B","C","D"...

    is it possible ? how ?

    I'm trying to do a quoted transpose basically.

    is there any other easy tool which can do this ?
    So you want output literally as:


    B
    C
    D
    E
    1
    "A"
    "B"
    "C"
    "D"


    Do I have that right?
    Dave

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Excel data manipulation

    If so modify Pete's

    With source in A2:A5 in B1 and across
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    02-02-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Excel data manipulation

    Quote Originally Posted by FlameRetired View Post
    If so modify Pete's

    With source in A2:A5 in B1 and across
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Now I'm confused. ...because your post triggered these dobuts

    1. This does not require transpose? yes/no

    2. or I have to do transpose first and then apply this formula?

    3. also in which cell I need to apply the formula ?


    see ..guys .......my requirement is very simple,
    I have a column data ... I just want to make it transposed and quoted. ...thats it.

    ex:

    Input:
    ------
    A
    B
    C
    D
    E

    output: "A","B","C","D","E"


    I am quite okay if this can be done using textpad or notepad++ automatically also ..I will do this for a large data set....as long as this works....this need not have to be done with excel if its that complicated in excel.
    Last edited by zerodegree; 01-18-2019 at 11:26 AM.

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel data manipulation

    He is saying, instead of transpose as I recommended in my post to use the formula =CHAR(34)&INDEX($A$2:$A$6,COLUMNS($B$1:B$1))&CHAR(34)
    in cell B1 and drag it toward the right and you will get "A" in B1 and "B" in C1 etc.
    hope that helps.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Excel data manipulation

    Both my post (#6) and Dave's (#10) make it clear - put the formula in B1. However, it could go in any cell outside the range of your data (e.g. in B7), then drag it across.

    Pete

+ 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. Data Manipulation in Excel
    By lscudder in forum Excel General
    Replies: 4
    Last Post: 05-19-2012, 03:36 PM
  2. Notepad data to Excel WB with manipulation
    By Archibald_SM in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-13-2011, 07:31 PM
  3. Excel Data Manipulation (Kinda like a Transpose)
    By ericy51 in forum Excel General
    Replies: 1
    Last Post: 07-14-2010, 08:19 PM
  4. CSV data manipulation in Excel - use a macro? equation? pivot table?
    By GBACS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2007, 01:59 AM
  5. Replies: 4
    Last Post: 06-09-2006, 03:56 PM
  6. data manipulation in access from Excel vba
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2006, 02:50 PM
  7. [SOLVED] For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation
    By vmegha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2005, 08:20 PM

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