Data from multiple rows

[SonnyDeLyte]

Hi everyone,

Still a newbie trying to figure things out.... how can I transform multiple rows of data into one and also add new columns to the single row with data from the multiple rows?

Original data:

EmpID	Name	Project ID	Month	Hours
111	John Doe	A	June	14
111	John Doe	A	July	16
111	John Doe	B	June	12
111	John Doe	B	July	13
222	Jane Smith	A	June	15
222	Jane Smith	A	July	14
222	Jane Smith	B	June	13
222	Jane Smith	B	July	14

Desired output:

EmpID	Name	Project ID	June Hours	July Hours	2 Month Total Hours	2 Month Avg Hours
111	John Doe	A	14	16	30	15
111	John Doe	B	12	13	25	12.5
222	Jane Smith	A	15	14	29	14.5
222	Jane Smith	B	13	14	27	13.5

Thanks in advance for any help or suggestions!

Sonny

[jdunkerley79]

Odd that we can't download.

I chose to use a Summarise over a formula so it would just keep working regardless of number of months or when the months change.

First thought was a formula tool but felt a summarise would be a more general solution.

[vishwa_0308]

You are right @jdunkerley79..using summarize tool will make it generic regardless of how many months are there.

[SonnyDeLyte]

Awesome - learned a lot today! Thanks everyone!!

[SonnyDeLyte]

I still can't download... :-(

If you get a chance, I'd be interested in seeing how you configured the Summarize tool to compare to the Formula tool.

Thanks!

[vishwa_0308]

Cross tab config will be same and for summarize tool do group by EmpID and use Sum and Average actions for hours field. then use a join tool to combine Cross tab and Summarize inputs on EmpID.

[jdunkerley79]

Config all three tools below:

[SonnyDeLyte]

Thanks again!

[cmcclellan]

Yes, I was doing the same as this :)