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 ?
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 ?
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
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.
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
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?An alternative is to employ Power Query/Get and Transform.
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
@GeofffW283
Good point. I missed that. PQ only works with 2010 and later versions.
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.
So you want output literally as: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 ?
B C D E 1 "A" "B" "C" "D"
Do I have that right?
Dave
If so modify Pete's
With source in A2:A5 in B1 and acrossFormula: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.
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.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks