
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 15, 2017 04:16 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 16, 2017 12:19 AM
It’s possible, but not very pretty. A very quick overview would be
- Either Lookup or Rollup (with ARRAYJOIN) the column with those values; this gets you a ginormous string with each entry, divided by commas.
- For every entity you wish to count, create a formula column with the formula
(LEN(','&RollUpStr)-LEN(SUBSTITUTE(','&RollUpStr,','&Entity,''))/LEN(','&Entity)
- 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…

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 16, 2017 09:27 AM
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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 18, 2017 06:20 AM
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?
