Reduce duplicates in one table to unique and count in another

[Jon_Richings]

I have a table that looks like this:

4 Default BFI-1.00-T
5 Default BFI-1.00-P-12
6 Default BFI-1.00-P-12
7 Default BFI-1.00-T
8 Default BFI-1.25-1.00-R
9 Default BFI-1.25-1.00-R
10 Default BFI-1.00-P-12

You can see that there are several entries where the third field is the same. How can I convert this to a table where each row is unique but adds a field that shows how times each entry is repeated in the first table. Basically it’s a parts list. I want to be able to see:

BFI-1.00-P-12 3
BFI-1.00-T 2
BFI-1.25-1.00-R 2

and so on.

[W_Vann_Hall]

@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…

[Jon_Richings]

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?

[Andy_Lin1]

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?