checking duplicates

[mirkolando]

Hi Airtable Community, 

The problem is to check in Airtable whether a value in a column (e.g. column 2) occurs **for the first time** or whether it is a duplicate. If the value is the **first occurrence**, a value from another column (e.g. column 4) should be displayed in another column (e.g. column 3). However, if the value is a duplicate (i.e. it already occurs in column 2 in an earlier row), column 4 should remain empty.

Happy for any ideas to solve this 🙂 

Thanks & best

Mirko

[kuovonne]

Formula fields in Airtable cannot reference data in other records, so you cannot do this with only a formula field.

If values in {Column 2} do not change, you could have an automation that does a "Find records" action based on the {Column 2} value, and then if a duplicate is found, have an "Update Record" action that copies over the value you want.

[Hamlin_Krewson2]

@kuovonne is right in that Formula fields, on their own, cannot reference data from other records. Airtable does, however, give us the Rollup field which can utilize most (if not all) of the same options a standard formula field can. It isn't straightforward, though. You still need a relationship that captures all of the records you want to test. Once you have that, it's a complex algorithm to test for duplicates. 

For instance, I have an assets base of computers. Each computer gets linked to an asset type (to define the model). In the asset type table, I have a lookup of the serial#s from the linked records. This gives me a list of all serial numbers. 

Then in my assets table, I can build a rollup formula to test with an output of (0|1|2) to show if the serial exists, if it's unique, or if it's duplicated. The basic algorithm for this test is:

If serial# is empty, value = 0
	else If we find a second copy of serial#
		then true, value = 2
		else If we find a copy of serial (sanity check),
				then true, value = 1

 The formula for my need then looks like:

IF(
	NOT(
		{Serial #}
	),
	VALUE('0'),
	IF(#If we find a second copy of serial
		FIND( 
			{Serial #},
			ARRAYJOIN(values, ";")&'',
			FIND( 
				{Serial #},
				ARRAYJOIN(values, ";")&''
			) + LEN(
				{Serial #}
				) + 1
			),
		VALUE('2'),
		IF(
			FIND(
				{Serial #},
				ARRAYJOIN(values, ";")&''),
			VALUE('1')
		)
	)
)

So, for your example.

• TableA
  • Column2 contains the data to dedupe
  • Column4 contains data to display on first occurance in column3
  • Column3 is a Rollup field using the aggregation formula shown below.
  • Column5 a link to another table (TableB), all rows in TableA link to the record "DeDupe"
• TableB
  • Contains one record "DeDupe"
  • Column2 is the linked column from TableA
  • Column3 is a lookup set to look up the data from TableA::Column2

IF(
	NOT(
		{Column2}
	),
	'',
	IF(
		FIND( 
			{Column2},
			ARRAYJOIN(values, ";")&'',
			FIND( 
				{Column2},
				ARRAYJOIN(values, ";")&''
			) + LEN(
				{Column2}
				) + 1
			),
		'',
		IF(
			FIND(
				{Column2},
				ARRAYJOIN(values, ";")&''),
			{Column3}
		)
	)
)