reverse pivot / transpossing

[Ludavhen]

Hi (again)
I have data in a sheet in this format:
A1: country
B1: city
C1..N1 : month (jan. feb, etc)
A2..A100: various country names
B2..B100: various city names
C1..N100: values

Now, wat I need is to have this data in the following format:
A1: Country
B1: city
C1: month (specific)
d1: Value

To clarify, the resoults should look like this:

Country city month value
Germany Berlin jan 100
Germany Berlin feb 50
etc..

any tips?

regards,

 

[Tom Ogilvy]

You can look at John Walkenbach's site and see if this tip will work:

http://j-walk.com/ss/excel/usertips/tip068.htm
Creating a database table from a summary table

Your situation is a little more complex but you might be able to adapt it.

 

[Ludavhen]

Thanks Tom,
I had earlier looked at this atricle and tried to adapt it to my needs but
with no succes.

rgrds

 

[Debra Dalgleish]

Insert a column between B and C
Add a column heading, e.g. AllData
In cell C1, enter the formula: =A2&"$"&B2
Copy the formula down to row 100
Follow the steps in the article that Tom suggested, using cells C1:O100
as the range

As a final step, move the AllData column to the far right of the newly
created table, and use Data>Text to Columns to split the data into
country and city, using the $ as the delimiter.

 

[Harlan Grove]

Hi (again)
I have data in a sheet in this format:
A1: country
B1: city
C1..N1 : month (jan. feb, etc)
A2..A100: various country names
B2..B100: various city names
C1..N100: values

Now, wat I need is to have this data in the following format:
A1: Country
B1: city
C1: month (specific)
d1: Value

....

This can be done with formulas, and it's not so difficult. If your output
range were on a different worksheet (as it seems it must be given your range
addresses), give the original table a name, like Tbl. Then in the other
worksheet enter these formulas.

A11 as you've already entered them.

A2:
=INDEX(Tbl,INT((ROW()-2)/12+2),1)

B2:
=INDEX(Tbl,INT((ROW()-2)/12+2),2)

C2:
=INDEX(Tbl,1,MOD(ROW()-2,12)+3)

D2:
=HLOOKUP(C2,Tbl,INT((ROW()-2)/12+2),0)

Select A22, Edit > Copy, use Edit > GoTo to select A31189, and Edit >
Paste.

The advantage of this approach over pivot tables is that text values in the
grid, C2:N100, would be pulled into the new table correctly. Pivot tables
can only handle numbers.

 

[Ludavhen]

Perfect, just waht I needed. Thanks a lot.
I did have some problems as my computer at home is in dutch..but luckily I
could log into the company's network where we have MSoffice in English.
Now I only need to understand the formulas but I beleive I will be able
to work them out.
Thanks again.

 

[Ludavhen]

Hi Debra, thnks for your input. I had tried that, but was not exactly what
I was looking for.
Harlan Grove has given me the procedure I needed (see further in the
thread).
regards,