| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Columns to Rows
I have a worksheet that I would like to change all rows to columns -- a
pivottable would work perfectly if text could be shown in the data area. Is there a way to do this? If not, my table has 3 columns: Key Code Country Text Code 1 US xxxxxxxxx Code 1 China xxxxxxxx Code 1 UK xxxxxxxxxx Code 2 US xxxxxx Code 3 China xxxxxx Code 3 UK xxxxxx I want change the table to be Code US China UK 1 xxxxxx xxxxxx xxxxxx 2 xxxxxxx 3 xxxxxx xxxxxx Can someone tell me how that can be done??? Thank you! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text Columns to Rows
Suppose the source table as posted is in A1:C7
and the new table (below) is set out in A9:D12 Code US China UK 1 xxxxxx xxxxxx xxxxxx 2 xxxxxxx 3 xxxxxx xxxxxx Put in B10, array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(ISNA(MATCH($A$9&" "&$A10&"_"&B$9,$A$2:$A$7&"_"&$B$2:$B$7,0)),"",INDE X($C$2:$C$7,MATCH($A$9&" "&$A10&"_"&B$9,$A$2:$A$7&"_"&$B$2:$B$7,0))) Copy B10 across/fill down to D12 to populate. Adapt to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cwwaters" wrote: I have a worksheet that I would like to change all rows to columns -- a pivottable would work perfectly if text could be shown in the data area. Is there a way to do this? If not, my table has 3 columns: Key Code Country Text Code 1 US xxxxxxxxx Code 1 China xxxxxxxx Code 1 UK xxxxxxxxxx Code 2 US xxxxxx Code 3 China xxxxxx Code 3 UK xxxxxx I want change the table to be Code US China UK 1 xxxxxx xxxxxx xxxxxx 2 xxxxxxx 3 xxxxxx xxxxxx Can someone tell me how that can be done??? Thank you! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text Columns to Rows
So, in b10 I make an array??? Sorry, I understand what you are doing, but get
lost on building an array, and then copying that function. Thank you!!!! "Max" wrote: Suppose the source table as posted is in A1:C7 and the new table (below) is set out in A9:D12 Code US China UK 1 xxxxxx xxxxxx xxxxxx 2 xxxxxxx 3 xxxxxx xxxxxx Put in B10, array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(ISNA(MATCH($A$9&" "&$A10&"_"&B$9,$A$2:$A$7&"_"&$B$2:$B$7,0)),"",INDE X($C$2:$C$7,MATCH($A$9&" "&$A10&"_"&B$9,$A$2:$A$7&"_"&$B$2:$B$7,0))) Copy B10 across/fill down to D12 to populate. Adapt to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cwwaters" wrote: I have a worksheet that I would like to change all rows to columns -- a pivottable would work perfectly if text could be shown in the data area. Is there a way to do this? If not, my table has 3 columns: Key Code Country Text Code 1 US xxxxxxxxx Code 1 China xxxxxxxx Code 1 UK xxxxxxxxxx Code 2 US xxxxxx Code 3 China xxxxxx Code 3 UK xxxxxx I want change the table to be Code US China UK 1 xxxxxx xxxxxx xxxxxx 2 xxxxxxx 3 xxxxxx xxxxxx Can someone tell me how that can be done??? Thank you! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text Columns to Rows
Here's a sample construct to illustrate:
http://www.freefilehosting.net/download/3b5k9 2 col lookup.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cwwaters" wrote: So, in b10 I make an array??? Sorry, I understand what you are doing, but get lost on building an array, and then copying that function. Thank you!!!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text Columns to Rows
You *can* use pivot table.
You just have to convert PT to List with a bit of VBA. Excel 2003. http://www.freefilehosting.net/download/3b61d |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Wrap Text Across Columns & Rows | Excel Discussion (Misc queries) | |||
| How to copy some text in columns and rest in rows | Excel Discussion (Misc queries) | |||
| Each rows last cell text value could be in any columns. | Excel Worksheet Functions | |||
| Converting text from rows to columns | Excel Worksheet Functions | |||
| text data in one column many rows to many columns one row | Excel Discussion (Misc queries) | |||
Linear Mode
