Tricky formula to crack anyone please?

Ripul,

It’s not exactly a “formula” per se that will do what you want because it’s not a spreadsheet and you can’t directly reference other records. There’s a way to do it though by processing the values into a 2nd ‘Calc’ table. The base link does the following, feel free to copy base to your own workspace:

• Adds a sequential number to identical integers (or text).
• Need to link every record to the Calc sheet with a “.”
• ‘IDPadded’ is used as a unique identifier to create an array in ‘Calc’ table.
• Airtable is a database, not a spreadsheet. The records have no dimensional relation to each other like a spreadsheet, so you can’t directly reference a location on the grid. The grid only “looks” like a spreadsheet, every record is independent.
• To get relative position, need to pass each record into a 2nd table as an array, then locate the position of the values in string using ‘Find’.
• ‘FindArray’ finds the location of ‘IDPadded’ in the array to sequence the next number for ‘IndexNoGaps’.
• ‘IndexNoGaps’ sequences the next number in the same order that you link to the calc sheet; also recalculates if you delete a middle record (“row” in excel terms). This acts as an index to make a 2nd array with values in calc table.
• ‘Prev#’ rollups find the previous values in the sequence, - # records.
• ‘Decimal’ formula is a nested IF formula to check how far back the values are identical. This base only checks the previous 10 records that are fed into the ‘Calc’ table, but could be expanded easily by adding more ‘Prev#’ fields (“column” in excel terms) and adding to the nested IF ‘Decimal’ formula.