Caveat: This methodology only works with one-dimensional data range i.e. either 1 row or 1 column at a time.

Also see: Our walkthrough on how to remove duplicates using UPDATE function

# Problem

There’s either a row or a column of data with duplicate values and you wish to have a way to get only the unique ones in another row/column, as needed.

I get it - if we were on Google Sheets, the simplest / quickest way to achieve this task would’ve been to use their UNIQUE function (no pun intended )

While at dashdash we’re still working on bringing you as much flexibility as you may need to have the best experience, here’s how you can achieve this task from our dashboard, today.

# Solution

## Overview

- First, convert the range to a JSON by keeping the
`header`

(keys) and`data`

(values) same - half the job is done here- …more on this in the
**Implementation**section

- …more on this in the
- Second task would be towards housekeeping activities i.e. replace garbage values such as colon, brackets etc. so that you’re only left with a set of comma separated values
- Finally, use our recently released SPLIT function to distribute the data either on a particular row/column

## Demo

### Formula used in cell B2

`=SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RANGE2JSON(A2:A,A2:A),':"null"',""),'"',""),"[{",""),"}]",""),",",1)`

## Implementation

Say, we have a range of duplicate data values in Column A starting from row 2 (row 1 would have headers so it should be ignored). In that case, here’s how we can go about getting the unique set of values in Column B (again, starting at row 2).

### Step 1: Convert range to JSON

`B2: =RANGE2JSON(A2:A,A2:A)`

As you can see from the output below, the deduplication automatically occurrs during this process

### Step 2: Remove unnecessary values

The actual text output from **step 1** looks something like this -

```
[
{
"Jane": "null",
"John": "null",
"Jess": "null",
"Joe": "null",
"Joy": "null",
"Jay": "null"
}
]
```

If you spreadsheets as much as I do, you can now imagine that the task is simply to `SUBSTITUTE`

values like colon `:`

, the string value `"null"`

, all the brackets from above and below & replace them with eternal nothingness - all of which is done using the formula below:

`B2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RANGE2JSON(A2:A,A2:A),':"null"',""),'"',""),"[{",""),"}]","")`

Note that the

`RANGE2JSON`

function has simply been encapsulated by a combination of`SUBSTITUTE`

functions

Output in cell B2 would now look something like this -

`Jane,John,Jess,Joe,Joy,Jay`

### Step 3: Split 'em

Here’s the syntax to use our SPLIT function, which is supercharged with the ability to transpose on-the-go via an optional “orientation” option!

All we have to do now is replace the static values from the above image with the formula that we have from **step 2**, which would then look something like this -

`B2: =SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RANGE2JSON(A2:A,A2:A),':"null"',""),'"',""),"[{",""),"}]",""),",",1)`

The final / resulting output would be the same as shown in the **Demo** section

## But what about rows?

You can follow the same steps from above that has a row range with duplicate values and have the output written back in a row simply by adjusting the range within the `RANGE2JSON`

formula & by tweaking the `SPLIT`

function’s “orientation” option -

- set it to 0 (default) for the data to be written horizontally i.e. in a row, or
- set it to 1 for the data to be written vertically i.e. in a column

Feel free to comment in case you need any assist with having this implemented in your spreadsheet on dashdash.

In case you just landed on this article and don’t yet have an account with us, do sign up here to get started!

Cheers,

Sourabh | No-Code Builder