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

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();
}


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.


You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.






Comments