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) anddata
(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 ofSUBSTITUTE
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