rearrange data for charting

[kaestner.ralf]

Hi Group,

I have data like this:

iteration; name; data1; data2
1;abc;5;6
2;abc;100,200
1;xyz;50,60
2;xyz;1000,2000

and want to re-arrange it (into a new sheet) to get a series chart out of that re-arranged data. the re-arranged data would look like this:

iteration; data1_for_abc; data2_for_abc; data1_for_xyz; data2_for_xyz
1;5;50;6;200
2;100;1000;60;2000

How would I do that when "iteration" is always an identical range (like 1-21) for each "name" and there is a random amount of names with identical count of data fields for each name.

I already tried to get help for this by googling quite a lot but did not really find an answer, even if I am almost sure this is a quite common excel problem. So maybe you can just point me into the right direction because I suck in putting the right search text phrase into google

I tried noddling arround with the offset() fx but did not really get it to work due to my excel noobness =)

Can I even get a result sheet without a macro?

 

[Ron Rosenfeld]

Hi Group,

I have data like this:

iteration; name; data1; data2
1;abc;5;6
2;abc;100,200
1;xyz;50,60
2;xyz;1000,2000

and want to re-arrange it (into a new sheet) to get a series chart out of that re-arranged data. the re-arranged data would look like this:

iteration; data1_for_abc; data2_for_abc; data1_for_xyz; data2_for_xyz
1;5;50;6;200
2;100;1000;60;2000

How would I do that when "iteration" is always an identical range (like 1-21) for each "name" and there is a random amount of names with identical count of data fields for each name.

I already tried to get help for this by googling quite a lot but did not really find an answer, even if I am almost sure this is a quite common excel problem. So maybe you can just point me into the right direction because I suck in putting the right search text phrase into google

I tried noddling arround with the offset() fx but did not really get it to work due to my excel noobness =)

Can I even get a result sheet without a macro?

A simple way would be to use a pivot table and/or a pivot chart.

You would drag the iteration to the Rows or Axis area; the Name to the Columns or Legends fields; and the "data" to the Values fields.
You'll probably want to DEselect the option to show grand totals.