Rollup and Count Duplicates - then output "count x name, count x name2"

[arne_nori]

Hi - i used search but only found different things asked … and the only similar question was closed and not aswered. … so i´ll try my luck here :slightly_smiling_face:

It basically is something like an inventory roundup or item count on an invoice. I want to create a rollup field that gives me the counts of duplicates in a group in another table.

Table 1 contains “roles”
I can have multiple lines with the same role. eg:
Field1 - Field2
Day1 - Role1
Day1 - Role1
Day1 - Role1
Day1 - Role2
Day1 - Role2
Day2 - Role1
Day2 - Role2

In table two i want to roll them up to something like:
Day1: 3x Role1, 2x Role2
Day2: 1X Role1, 1x Role2

i found a solution where all options are hard coded … that is not usable as there are over 500 “Roles” already and we need to add more over time.

any way of doing this? probably very easy and im just to blind to see the light …

thanks!

[arne_nori]

anyone? any idea? Is this a basic funtion, that i just dont get? or is this impossible to do?

[Justin_Barrett]

Unfortunately this isn’t possible with Airtable’s current formula functions. It would require more elaborate array parsing than Airtable is currently capable of. This could be done pretty easily with a script, though. The script could run either manually or automatically, depending on your needs. If you have a budget for development, message me and we can look at options.

[arne_nori]

hi justin, thanks a lot - but as this is just for my own overview of things - job related but with no extra budget - i will surrender and accept the limitations. thanks a lot for the heads up - now i can rest my search.
:slightly_smiling_face:

[kuovonne]

An Airtable formula/rollup cannot do the detailed summary that you want. However, it can give you

• the total number of entries COUNTALL(values)
• what the unique entries are ARRAYUNIQUE(values)
• the number of unique values COUNTALL(ARRAYUNIQUE(values))

So in your example,

7 entries (4 unique)
Day1 - Role1
Day1 - Role2
Day2 - Role1
Day2 - Role2

A formula field cannot tell you how many of each unique entries there are.