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!

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!

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!

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!!!!

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