exceldatabaseuniqueuniqueidentifier

Is there a function in Excel that returns if a value is unique within a column?

I am creating a metadata excel for a database of raw data files, I have entries like this:

station variable year_from year_to ID
Berlin T 1998 2001 T_berlin_1998
Berlin T 2002 2023 T_berlin_2002 !!
Berlin Q 2002 2023 Q_berlin_2002
Berlin-Pankow T 2002 2015 T_berlin_2002 !!
Berlin-Pankow Q 2001 2015 Q_berlin_2001

I thought I create a short ID like variable_first-six-letters-of-stationname_year (e.g. T_berlin_1998), which is easy to find for both computers and also human-readable (and not just a super long number). However, the ID has to be unique, so I would like to be able to check for that, because it could be that I get two ID's that are the same by the way I though of so far (e.g. T_berlin_2002.

Is there a function in Excel that returns if a value is unique within a column?

I found UNIQUE(), but that function returns the unique values from an array, which is not what I need, and I cannot currently think of a way to use it to get to the result I want. Ideally, I would get a warning (e.g. colour the cell red, output of a text) when the ID-formula (ID = variable&station&yearfrom) produces a value that already occurs in said column.

Solution

  • Conditional formatting rule to mark duplicates:

    =COUNTIF(E:E,E1)>1 applied to E:E range:

    enter image description here