Help

Re: Reduce duplicates in one table to unique and count in another

1198 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jon_Richings
6 - Interface Innovator
6 - Interface Innovator

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.

3 Replies 3

@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
6 - Interface Innovator
6 - Interface Innovator

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?