Checking for duplicates

[RVDL]

Hi Community,

Suppose I have the following sample data:

State	Code
Alabama	AL
Alaska	AK
Arizona	AL
Arkansas	AR
American Samoa	AS
California	CA
Colorado	CO
Connecticut	CA
Delaware	DE
District of Columbia	CA

I want to ensure each State has its own unique Code. I would like to show an error message if this isn't the case. Using the above data, two error message would be shown like:

Error: the following states can't have the same code: Alabama, Arizona

Error: the following states can't have the same code: California, Connecticut, District of Columbia

My thinking is to create a table with the codes that have duplicates, and a second column with the corresponding states. For example:

Code	States_Concat
AL	Alabama, Arizona
CA	California, Connecticut, District of Columbia

And then use the Message tool on above table. Any idea how to create above table? Also happy to see different approaches of course.

Thanks.

[abacon · Accepted solution]

@RVDL Here is a workflow that does what you are looking for. It checks if there are duplicates then formats the table in the way you are talking and includes the message tool to show the error message.

Bacon

[aatalai · Accepted solution]

@RVDL slightly different way to @abacon , count how many states that there are to each distinct code, then filter for those that aren't equal to 1, then join those to the original data set, then generate the error message using the summaries tool and conactating and grouping and use the message tool

[flying008 · Accepted solution]

Hi, @RVDL 

[RVDL]

@abacon 

@aatalai 

@flying008 

All different approaches work fine. Thanks for helping.