Codes without a lookup

• The system that produced the export stores codes and keeps their meanings in a separate table that was not exported.
• The meanings lived in a manual, a form, or one person's memory.
• New codes were added over time without anyone updating the list.

• Nobody outside the original team can read the column, so it is ignored or misread.
• Reports group by code, and readers supply their own idea of what each code means.
• Two files that use the same code for different things are combined as if they agreed.

• Look for a column of short values with few distinct entries and no obvious meaning.
• Search the rest of the workbook and the source system for a sheet or table that pairs each code with a label.
• Count rows per code. A code that appears only a handful of times is often a late addition missing from any list.

• Find the lookup at the source: the system's reference table, its documentation, or the people who enter the data.
• Add the meaning as a new column and keep the original code, so the translation can be checked later.
• Leave undocumented codes flagged. Inferring a meaning from the rows that carry the code is a guess and should be labeled as one.
• Store the lookup with the data so the next person does not have to find it again.

• Categories that changed meaningA label stays the same across the file, but what it covers was redefined partway through.
• Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
• Free text in a coded fieldA column meant to hold one of a few fixed values also holds comments, explanations, and whole sentences.

TermsLookup table, Reference data, Data dictionary, Crosswalk