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

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 🙂

Company status.yxmd