moving values from one column to another according to a logic

[Gio_ita43]

Hi everybody,
I have got a really simple question.

My data are:

A B C
Name X Y
TF_80 7 35.2
TF_80 14 17.5
TF_80 21 65.4
TF_80 28 41.2
TG_99 7 54
TG_99 14 77
TG_99 21 15
TG_99 28 32
TR_43 7 49
TR_43 14 2
TR_43 21 24
TR_43 28 96

And I would like to obtain something like that:

A B C D
X TF_80 TG_99 TR_43
7 35.2 54 49
14 17.5 77 2
21 65.4 15 24
28 41.2 32 96


Basically I would like to move the values of the 1st column which have the same label, into a new column on the right with respect to the values on the second column (which are timesteps).

It should be quite an easy operation but I am a newbie. Maybe I should use something like lookup...
Please note that in this case I have only 3 different labels (TF_80, TG_99, TR_43) but in my original file I have got 38 labels so I would like a function that can be dragged.

Any help will be really appreciated.
Thanks.

[NBVC]

Create a Pivot Table.

Select the range and go to Data|Pivot Table and Pivot Chart Report...

Click Next, and confirm range

Click Next Enter where you want the table to go, and click Layout

Drag the X column to the Row area. Drag the Name column to the Column area and drag the Y column to the Data area and click Finish.

You can hide the Grand Total Column and Row by right-click their respective column headers and selecting Hide.

Pivot Table tutorial

[JBeaucaire]

Here are two ways.

PivotTable
1) Highlight A:C
2) Add a Data > PivotTable
3) Put the columns into the sections as shown and the result is as shown on the sample sheet.



Formulas
Each colored section if a new added formula.

1) Add a key formula, in D2 then copied down: =A2&"-"&B2

2) In G1 add an array formula:
=INDEX($A$1:$A$100,MATCH(0,COUNTIF($A$1:$A$100,"<"&$A$1:$A$100),0))
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array.

3) In H1, add another array formula:

=IF(COUNTIF($A$1:$A$100,">"&G1), INDEX($A$1:$A$100, MATCH(COUNTIF($A$1:$A$100,"<="&G1), COUNTIF($A$1:$A$100,"<"&$A$1:$A$100),0)),"")
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array.

4) The H1 cell is copied to the right and each unique value should appear

5) In H2 enter this normal formula, then copy it down/across the table:
=INDEX($C:$C, MATCH(H$1&"-"&$G2,$D:$D, 0))

[Gio_ita43]

Thanks very much, I gave you both of them a reputation feedback.

If I may I would like to ask you something else.

Once that I got my pivot table I would like to produce a plot for every column against the time step in the first column.
I would like each of this plot in a separate chart with the label of the column (e.g. TF_80).

Would I have to use a VBA Macro right?
I was thinking to record the Macro for one plot and then somehow extending it for all the remaining columns.

Any advice on that?

Thanks.

[Gio_ita43]

I will post the question on the programming section of the forum and marked this post as solved. Thanks.

[Gio_ita43]

Sorry guys I ran into another small problem while creating my pivot table.

I am creating a macro for creating my pivot table.
Since the range of data that I import every time can vary when selecting the range of data I want to select "until you find values in the columns". I realized that selecting the entire columns, thus also cells without values it returns a pivot table with all 1.

when it asks me the Table/range:
I select:

Sheet1!A1:C19315

Actually I would like to change this
C19315
and make it as the length of the column with values. In this case it was 19315, some other times can be less or larger.


Thank you so much.

[NBVC]

You can create a dynamic named range.

Go to Insert|Name|Define and enter a name for your table, e.g. MyTable

and in the refers to field enter formula like:

=Sheet1!$A$1:INDEX(Sheet1!$C:$C,MATCH(9.99999E+307,Sheet1!$C:$C))

adjust the Sheetname to suit.

Now you can use MyTable as your PivotTable range.