Home > Back-end >  Removing repetitive values in google sheets
Removing repetitive values in google sheets

Time:06-03

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))
  • Related