How to use cDataColumn.uniqueValues() to create a list
The .uniqueValues() method of cDataColumn will return a collection of cells – one for each unique value in the column. This is a really simple way of creating a look up table from a set of data. Here’s how to extract the unique values from one sheet and create a column of them in another
VBA
Private Sub uniqueCells()
Dim dSet As New cDataSet, r As Range, cc As cCell
Set r = firstCell(wholeSheet("messAround"))
With dSet.populateData(wholeSheet("Sankey"))
r.Value = "sourceLabel"
If Not .Where Is Nothing Then
For Each cc In .column("sourceLabel").uniqueValues()
Set r = r.Offset(1)
r.Value = cc.Value
Next cc
End If
End With
End Sub
Google Apps Script
As per all examples on this site, I use caching for Google Apps Script.
function uniqueCells() {
var cache = sheetCache(“messAround”);
var ds = new cDataSet().populateData(wholeSheet(“Sankey”));
cache.setValue(“sourceLabel”,1,1);
if (ds.where()){
DebugPrint(ds.column(“sourceLabel”),sad(ds.where()));
ds.column(“sourceLabel”).uniqueValues().forEach(
function (item, index+1) {
cache.setValue(item.value(),index,1);
}
);
}
cache.close();
}
[/sourecode]
Walkthrough
Having populated the DataSet that represents the contents of the sheet ‘Sankey’, each unique value in the “sourceLabel” column is copied to a column in the “messAround” sheet, after giving the column a title.
Sorting the values
.
,uniqueValues() takes an optional argument that selects the kind of sorting required as below. By default the list is presented in the order encountered.
Take a look at
One Liners for more tips like this. In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.