Finding Missing Numbers

Topic Labels: Formulas
1247 4
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I have a number for every record in this format P01384. It starts with P00000. There are several records missing and I would like to see if there is a way to write a formula that would give me the numbers of the missing records in the sequence. Is this possible in Airtable?. I have found a way to do it exporting a .csv of the table and then, using a macro I found for Excel, find the missing numbers. A very convoluted way. Would love to just have this info in Airtable. Seems like it should be a simple thing but guess it’s not. Any guidance would be greatly appreciated.

4 Replies 4

Hey there! There are definitely a few ways you could do this, but before I suggest a technical method, like using the scripting block, would you mind sharing a sample of the data and any linked tables?

4 - Data Explorer
4 - Data Explorer

@andywingrave Thank you so much for answering back. Here is the way the table is setup. No linked tables.

Screen Shot 2020-04-29 at 7.55.06 AM

Example of a missing record. The scripting block sounds like a good idea. I have no scripting experience but can start looking to learn what I need to get this done.

The record number in the screen capture is a formula that is a reference to a single line text field. It is a reference as a deterrent for users to change this by mistake.

Yeah - For a technical (long-term) solution - I think the scripting block is a good shout here - You could create some logic that says if Record A != Record B - 1, create Record C called “Record A +1” - Or something like that…


Very simply, if you’re only going to do this once…Create a new table of all the possible record values P1-P10000000, sort them alphabetically - Create a link to your original table and see which ones are missing. Once you have Change the “link-to” back to text, and you’ll have all your missing values. A bit more manual - But no scripting required.

Thank you so much. Not sure I understand how to proceed with the second, simpler solution. Will try to investigate more and see if I can figure it out. Incredibly grateful for your help.