Grouping FSA(Postal Code First 3 Digits) Together - How to deduplicate?

Question

Hi everyone, I am trying to group up FSAs (first 3 letters of a Canadian postal code) that are next to each other. I tried grouping them based on the distance to each other(less than 3 KM) and then using a multi-row formula to manage deduplication but am running into issues with the duplicates in the two FSAs since I appended FSA to FSA for a many to many join.

Is there a way to manage this in a multi-row formula or is there something else I should try?

My target output is to create a list of unique FSAs with a column called Grouping that would list the main FSA that it is linked to as it is within 3 KM of each other. For example:

Grouping FSA
B1E B1E
B1E B1G
B3H B3H
B3H B3J
B3H B3P
NULL A0A - No FSA that is within 3 KM of it.

I'm including the workflow, a goal output and the input file

Postal Codes Next to Each Other.yxmd

FSACentroids.csv

JessicaXChung · Answer

Hi Qiu, not quite. I want to group up the postal codes that are close to each other(within 3 KM). The problem in the initial summarize I had created is:

* If a FSA is next to another FSA then there would be two rows because one FSA will list the second FSA as part of the append and the second FSA in it's own row will list the first FSA 
* In the Summarize tool output, the grouping FSA (CFSAUID) can appear in Concat_Source_CFSAUID, I need to remove any mention of the FSA (CFSAUID) from the values in Concat_Source_CFSAUID

For example in the Summarize tool output:

B3H B3J,B3P 2
B3J B3H 1

The goal is for me to have only one row B3H B3J,B3P and to remove B3J B3H. When unsummarized, the output should look like:

Grouping, FSA

B3H, B3J

B3H, B3P

Ideally it would make a row for B3H as well where it belongs to Grouping B3H but I can always make that separately

Qiu · Answer

@JessicaXChung 
If I understand you correctly, you want to create the unique FSA groups that dispite the order of FSAs in each group?

we can do the Sorting and take unique as below.

Postal Codes Next to Each Other-r1.yxzp