Match String separated by comma despite order of numbers

[Nmassarone]

Hello, looking for help on this to see if this is a possibility.

I have 2 strings in different columns where the numbers are separated by a comma and trying to compare those 2 strings to see if they match despite the order of the numbers.

i posted an example table below.

I have an account number field and then in the rep column is the list if ID numbers separated by a comma.

Im trying to compare the reps column to the reps in split column to get back a TRUE/FALSE if they match

For example the 1st row acct 1234 the 2 reps match exact so that would be TRUE. 

The 2nd account 5678 the reps match but the order of the reps might not be exact they might be flip flopped (33484 might come first in the reps_in_split column)

The 3rd account 5644 the order might be different in this one but i would still want back a TRUE because all the repsmatch there just not in the same order as the reps column.

Example starting table

Acct	reps	split id	reps_in_split
1234	489711,12288	S0123	489711,12288
5678	644071,33484	S0654	33484,644071
5644	21001,69059,67194	S0912	69059,21001,67194
987654	7663,65311,0504,62066	CC456	62066,65311,0504
346535	620653,621056	AA658	621056,620653
8743	34412,33484	S0221	33484,76871
4356854	82554,40461	CC692	40461,82554

Example of output

Acct	reps	split id	reps_in_split	MATCH
1234	489711,12288	S0123	489711,12288	TRUE
5678	644071,33484	S0654	33484,644071	TRUE
5644	21001,69059,67194	S0912	69059,21001,67194	TRUE
987654	7663,65311,0504,62066	CC456	62066,65311,0504	FALSE
346535	620653,621056	AA658	621056,620653	TRUE
8743	34412,33484	S0221	33484,76871	FALSE
4356854	82554,40461	CC692	40461,82554	TRUE

Hoping someone can help with this i really appreciate anyone taking the time to look into this.

thanks.

[DataNath · Accepted solution]

Would this work for you? Basically just splits and sorts both fields and then checks whether or not they're equal when rejoined.

[Nmassarone]

@DataNath perfect this works great appreciate the help on this

thank you

[DataNath]

Happy to help!

Just shout if you need any further help. If that’s solved the issue then feel free to mark it as the solution! :)