Creating Unique Record IDs Based on Criteria

[lil_t]

Hi there,

I'm trying to create unique record IDs in order to group Actual and Plan.

Department #	Description	Work Number	Project Description	Month	Category	Number of Employees	Number of Interns
1	Cat	A-123	ABC Project	January,2019	Actual	18	5264
1	Cat	A-124	ABC Project	January,2019	Plan	17	7641
3	Dog	A-125	ABCD Project	January,2019	Actual	18	5264
3	Dog	A-126	ABCD Project	January,2019	Plan	17	7641

I'd like the final table to look like this:

Department #	Description	Work Number	Project Description	Month	Actual Number of Employees	Planned Number of Employees	Actual Number of Interns	Planned Number of Interns
1	Cat	A-123	ABC Project	January,2019	18	17	5264	5264
3	Dog	A-125	ABCD Project	January,2019	18	17	5264	7641

So far, what I have created for my workflow looks like this:

Department #	Cost Center Description	Work Number	Project Description	Month	Actual Number of Employees	Planned Number of Employees	Planned Number of Interns	Actual Number of Interns
1	Cat	A-123	ABC Project	January,2019	18	[Null]	[Null]	5264
1	Cat	A-123	ABC Project	January,2019	[Null]	17	5264	[Null]
3	Dog	A-125	ABCD Project	January,2019	18	[Null]	[Null]	7641
3	Dog	A-125	ABCD Project	January,2019	[Null]	17	5264	[Null]

How should I go about this? 

Thanks!

[bpatel]

hi @lil_t ,

using your current data table that you have created you can use a summarize tool to get your final table.

i hope this helps or is on the right track!

[ConnorK]

Hi @lil_t ,

It looks like you will want to cross-tab your category column. Please see if this example helps.

Best,

[fmvizcaino · Accepted solution]

Hi @lil_t ,

Attached is an example showing how to get that done.

I've used a multi-row tool in the middle to standardize the worknumber and the rest was a matter of cross-tabbing and transposing.

Let me know if that works for you.

Best,

Fernando Vizcaino

[lil_t]

Thank you fmvizcaino for the answer, it worked! Thank you everyone for your solutions! I really appreciate it.