Help

Count unique values in a column by another column

Topic Labels: Data Formulas
316 1
cancel
Showing results for 
Search instead for 
Did you mean: 
ashleymf1983
4 - Data Explorer
4 - Data Explorer

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:

ashleymf1983_1-1701440972302.png

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!

 

1 Reply 1
ProsperSpark
5 - Automation Enthusiast
5 - Automation Enthusiast

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