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.
user24435177 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.