Convert rows to columns problem

[saveu90]

Hello to everyone, i'm news here. I come from Italy, Rome, and i'm sorry if i could make mistake with your language.

I start expalining my problem. As you can guess from the title, it concerns the convertation of rows/columns to columns/rows.

excel problem.jpg

As you can see from the image, i have a full list of countries with theirs respective indicators of agriculture, industry and services (in percentage of GDP).

Now, i tried almost everything (and of course the special paste button) but it results almost impossible to create a new table with every percentage situated in 3 columns : agriculture, industry and services. I attach on image to make it easier to understand.

problem.jpg

Any help will be grateful.

edit : i attached the xlsx file, if anyone want to help me.

[azumi]

Dont' put image, more useful attach your small sample workbook, and that could be attract the solver see your post, click "Go Advanced" and find paperclip button to attach your file.

cheeers

[saveu90]

I attached the whole xlsx file, thank you.

[FDibbins]

Add a couble of extra columns after column B (3 should be enough)
Highlight all the data in column B
Select Data Tab/Text2Column/Delimited/next/Other/use : as the delimiter

This should give you a seperated list of "type" and %

Now in 1 of those helper columns (I used E), copy this all the way down...
=IF(A3="",E2,A3)

Then in your table, use this, copied down and adcross...
=INDEX($D$3:$D$931,MATCH($G3&H$2,INDEX($E$3:$E$931&$C$3:$C$931,0)))

(This assumes your counties startin G3 and your "type" starts in H3

	A	B	C	D	E	F	G	H	I	J
1	COUNTRY	GDP - COMPOSITION, BY SECTOR OF ORIGIN(%)
2							COUNTRY	AGRICULTURE	INDUSTRY	SERVICES
3	Afghanistan	agriculture:*20%*	agriculture	*20%*	Afghanistan		Afghanistan	*20%*	*25.6%*	*54.4%*
4		industry:*25.6%*	industry	*25.6%*	Afghanistan		Albania	*19.5%*	*12%*	0%
5		services:*54.4%*	services	*54.4%*	Afghanistan		Algeria
6		note:*data exclude opium production (2011 est.)	note	*data exclude opium production (2011 est.)	Afghanistan		American Samoa
7					Afghanistan		Andorra	20%	25.6%	54.40%
8	Albania	agriculture:*19.5%*	agriculture	*19.5%*	Albania		Angola	19.50%	12%	68.50%

[samba_ravi]

See the attached file

[saveu90]

Thank you both!! I'm so much grateful!

Just one question, could you expalin me the: =INDEX($D$3:$D$931,MATCH($G3&H$2,INDEX($E$3:$E$931&$C$3:$C$931,0))) function? So that next time i can solve my problem myself, without bothering other people?

[FDibbins]

=INDEX($D$3:$D$931,MATCH($G3&H$2,INDEX($E$3:$E$931&$C$3:$C$931,0)))

INDEX() returns the entry at the intesection ofr a given row number and column number...
=INDEX(A2:J10,2,4)
would give the contents at the intesection of row 3 (2 rows from A2 - A1 is the 1st row) and column D
or in your example...
=INDEX($D$3:$D$931,row???)
If the index range is only 1 column, then you can leave out teh last INDEX argument

So all we need to do is ID which row to use. For that, we use MATCH
MATCH is normally used on a single column (or row) of data...
=MATCH(criteria,range,0) the 0 is for an exact match
=MATCH($G3,$E$3:$E$931,0)

In your case, we need to match 2 criteria with 2columns of data, so I combined the criteria with &, and I brought in a 2nd INDEX() to combine the 2 criteria columns (so that using ARRAY was avoided)
=MATCH($G3&H$2,INDEX($E$3:$E$931&$C$3:$C$931,0))
That gives the row to plug into the INDEX...
=INDEX($D$3:$D$931,MATCH($G3&H$2,INDEX($E$3:$E$931&$C$3:$C$931,0)))