Sum and grouping of data

[Belletmns]

Hi Team,

I need  your help, I'm struggling with grouping my data based on certain conditions. I would like to achieve the pivot functionality in excel.

Here is my sample data:

Company	EE #	EE employment Status	C Status
Company 1	EE1	Active	Closed
Company 1	EE2	Terminated	Live
Company 2	EE3	Active	Closed
Company 2	EE4	Active	Live
Company 3	EE5	Terminated	Closed

Here is my preferred output:

	Active	Terminated	Grand Total
Row Labels	Closed	Live	Closed	Live
Company 1	1			1	2
Company 2	1	1			2
Company 3			1		1
Grand Total	2	1	1	1	5

Thanks in Advance!

[atcodedog05 · Accepted solution]

Hi @Belletmns 

This is how you can do it.

Workflow:

1. Using formula tool i generating column label contacting [EE employment Status] [C Status]. Creating a variable count = 1 which will be used for sum of count.

2. Using crosstab tool key as Company, Name as label, Value as count and aggregation method as Sum, Total row & Total Column.

3. Using dynamic rename to rename the columns 

Hope this helps 🙂

[Belletmns]

Thank you @atcodedog05 Cheers! 🍻

[Belletmns]

Hi @atcodedog05 ,

Need your help please, I need to add the year below Company Name. What steps should I add?

My data:

Company	Year	EE #	EE employment Status	C Status
Company 1	2016	EE1	Active	Closed
Company 1	2017	EE2	Terminated	Live
Company 2	2020	EE3	Active	Closed
Company 2	2021	EE4	Active	Live
Company 3	2022	EE5	Terminated	Closed

My Desired Output:

Row Labels	Active - Closed	Active - Live	Terminated - Closed	Terminated - Live	Grand Total
Company 1	1			1	2
2016	1
2017
Company 2	1	1			2
2020	1
2021		1
Company 3			1		1
2022		1
Grand Total	2	1	1	1	5

Thank you! 🙂