Count unique values in a column by another column

[ashleymf1983]

Hello All,

This is my first post here & I am an Airtable newbie.

I am trying to get my table to show the number of unique values for 1 column based on another column.

Here is an example of my data:

I want to insert a column that counts the number of unique TU ID's by the School & County column.

Thank you for your help!

[ProsperSpark]

Hi Ashley M,

I can't quite tell from your screenshot if a TU ID is a single digit or if each ID is a six-digit number. I would suggest putting a comma between each ID. 

If you want to count only unique numbers in the cell, Airtable's formula language doesn't directly support operations like splitting a string into an array for unique counts, but you can achieve this using a JavaScript script block.

Here's an example of how you might implement it:

// JavaScript block in Airtable

let uniqueIDs = {};

// Split the cell value into an array using the delimiter (assuming comma)
let idsArray = inputConfig.CellValue.split(',');

// Loop through the array and count unique IDs
idsArray.forEach(id => {
let trimmedID = id.trim();
if (trimmedID !== '') {
uniqueIDs[trimmedID] = true;
}
});

// Count of unique IDs
outputConfig.SetCell(uniqueIDs.length);

Please note that this example assumes that you have access to the scripting block feature in Airtable. If you don't have scripting capabilities, achieving this level of complexity might be challenging with the built-in formulas alone.

If you'd like some help - just let us know.  We'd be happy to take a look.  You can reach out at prosperspark.com