Transpose question

[ulrich_schumann]

Hi all,

I received a table that I need to alter, so that I can visualize the data.

I have data for the last years and every month is represented with additionally 3 columns at the end of the row.

Jan 2014 FTE | Jan 2014 hours | Jan 2014 net value ..... Dec 2016 FTE | Dec 2016 hours | Dec 2016 net value (and at the very end of each row the values are summed up).

There is one data row whenever one of these Month/Year columns is filled.

I want to transpose the data into something like:

Year | Month | FTE | hours | net value

and have a row wherever an FTE value is available.

I looked ínto the "Transpose" tool, but the data is not as expected. I assume that I need to extract year and months before but then how to match the data?

Not sure how to get this working.

Any ideas?

[pcatterson · Accepted solution]

I would first transpose, then parse through the three pieces of meaningful info from the previous header, then corss tab back on the type.  Take a look at the example.

[ulrich_schumann]

Thx. pcatterson - your are a data rockstar!

[sekarsdream]

Is there anyways where i can transpose rows to columns ,like creating columns by using row values?

Regards

Rajasekar

[DataBlender]

Hi @sekarsdream

That would be the crosstab tool

[sekarsdream]

No cross tab is not working out see i have close to 100 labels in rows and i want them to be made as columns , am not sure how to do that

[DataBlender]

How about something like this?

Otherwise can you share a few rows of data, together with how you would like it to look?

[sekarsdream]

Yeah sure , here is the file i want to transpose 

[sekarsdream]

i tried now in a different way it worked :) thanks a lot

[DataBlender]

Hi @sekarsdream,

Try this workflow