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 ConcatenatedEmp IDEMP IDEMP100069739500,EMP000097463728EMP000097463728EMP100069739500EMP000011837790EMP000011837790 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 IDCountEMP0000974637283EMP1000697395002

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

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

Tips and Tricks

Accepted answers

Haider

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.

Rvsd_Solution.yxmd

All comments

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

Solution.yxmd

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

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

Rvsd_Solution.yxmd

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157