Create a list of unique values

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
Page Content hide
1 VBA
2 Google Apps Script
3 Walkthrough
4 Sorting the values

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.