Get UNIQUE values from a range of data

sourabh · July 8, 2020, 5:05pm

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
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