Reduce duplicates in one table to unique and count in another

@Jon_Richings

It’s possible, but not very pretty. A very quick overview would be

1. Either Lookup or Rollup (with ARRAYJOIN) the column with those values; this gets you a ginormous string with each entry, divided by commas.
2. For every entity you wish to count, create a formula column with the formula
   (LEN(','&RollUpStr)-LEN(SUBSTITUTE(','&RollUpStr,','&Entity,''))/LEN(','&Entity)
3. Capture and present the data from item 2 as you see fit.

There’s a very detailed step-by-step in my reply here.
The pseudo barcharts in my Sales CRM Dashboard use a similar technique.
…and parts of this base and most definitely this base apply to your effort.

There are a number of caveats and gotchas, mainly having to do with counting overly similar entities. (For instance, consider the potential problem if you had parts BFI-1.25-1.00 and BFI-1.25-1.00-R.) And I’ve yet to see a solution for an unknown collection of items to count; instead, the items to be counted have to be known ahead of time. Of course, that’s largely true for a lot of Airtable processes – we need a loop…

W_Vann_Hall, Thank you! That is not a pretty workaround, but kudos for figuring it out. Unfortunately even though I create the item names, we are constantly adding new ones which would mean adjusting the code all the time. So I guess this doesn’t help. Darn. I would have thought this was a common requirement and I’m surprised to find that there is no common AT means of doing it. I absolutely love Airtable but keep hitting it’s limitations. Do you think the Pivot Table or any other of the new Blocks features would help?

The “native” method of handling this would be to create a new table with the values of the third field. That would get you the “parts list” automatically, and to do the count, you’d just use a count field.
Or is there a particular reason that third field has to remain text?