Split Entries in a column into multiple Columns and Give count of each in separate output

[ahsansalik]

Hi I have a very confusing task i have a column with Employee IDs concatenated in one column in an excel sheet as shown below i want them to be separated and placed in multiple columns highlighted as Bold with one Employee ID in each column. Also i want a separate output which gives me Employee ID and count of each instance of that employee ID 

Emp Id Concatenated	Emp ID	EMP ID
EMP100069739500,EMP000097463728	EMP000097463728	EMP100069739500
EMP000011837790	EMP000011837790
EMP000001378978
EMP000032097952
EMP000048084792,EMP000034938083,EMP000007950529
EMP000085815232,EMP000086381208
EMP000001595167,EMP000024663824,EMP000052109561
EMP000090248411,EMP000046375818,EMP000062958984
EMP000029093154,EMP000029134398,EMP000028805191
EMP000085815232,EMP000086381208
EMP000001595167,EMP000024663824,EMP000052109561
EMP000085831901,EMP000085996137
EMP000036077411,EMP000003052081,EMP000042597433,EMP000089835667
EMP000019726088,EMP000013222261,EMP000042320429,EMP000046545004
EMP100069739500,EMP000097463728

Output 2 should be calculated by counting each employee ID in  "Emp Id Concatenated" column and shown as below

Employee ID	Count
EMP000097463728	3
EMP100069739500	2

[DawnDuong]

Hi @ahsansalik 

one way to do this is:

1) use Text to Column tool, set to split into rows, with comma as delimeter.

this will generate one row for each instance of ID. For example there are 3+1+1+1 = 5 ID in the first 4 rows therefore it will give you 5 output rows 

2) connect a summary tool immediately after, grouped by ID, and count number of rows per ID (note: you should use Count, not Count Distinct)

to turbo charge your Alteryx skills, highly recommend that you check out the interactive lessons and recorded videos!

dawn 

[Haider]

Here is a solution based on your Data in "Emp Id Concatenated" field

[ahsansalik]

Haider Thanks for the Solution I was wondering can i get count for each of the splt column separately how would i summarize based on the split columns individually 

[Haider]

can you paste an example as in what exactly are you after based on your data? Do you want to count how many times an ID occurs in each cell/row?

[ahsansalik]

Yes i want count of how many times an Individual  ID has appeared in the Data. 

Input		Output
Emp Id Concatenated
EMP100069739500,EMP000097463728		EMP100069739500	1
EMP000029093154,EMP000029134398,EMP000028805191	Output Should Look Like	EMP000097463728	1
EMP000029093154,EMP000029134398,EMP000028805191		EMP000029093154	3
EMP000029093154,EMP000029134398,EMP000028805191		EMP000029093154	3
		EMP000028805191	3

[Haider · Accepted solution]

Ok here is the revised version contains both your original and new request. I think it does what you have mentioned. I have revised the original version with Dawn's suggestion - splitting to rows is much elegant. Noticed duplicates in your data, row 7 and 11 - I guess you are aware of them.

[ahsansalik]

Haider that is perfect mate what i wanted thanks a lot