I need to remove repetitive values (duplicates, triplicates etc) from a comma-separated list. There is a formula to do this :
=JOIN(",",UNIQUE(TRANSPOSE(SPLIT(A1,","))))
but this formula removes one of the duplicates, and leave the other one in the list. I would like to remove all repetitive values in a list.
For example; in column A:
28,30,35,37,37,39,45,46,48,50,52
1,3,8,10,10,12,19,21,45,45,45,47
would be come in column B:
28,30,35,39,45,46,48,50,52 (37s removed)
1,3,8,12,19,21,38,40,47 (10s and 45s removed)
How can I do this in google sheets? any suggestions? please thanks in advance.
CodePudding user response:
Use the [exactly once] optional argument to UNIQUE:
=JOIN(",",UNIQUE(TRANSPOSE(SPLIT(A1,",")),,1))