83
edits
Excel Data Quality Toolkit: Difference between revisions
→Misspelled or Invalid Taxonomic Names
Jegelewicz (talk | contribs) |
Jegelewicz (talk | contribs) |
||
| Line 170: | Line 170: | ||
'''Problem:''' Scientific names are misspelled, resulting in poor matching of taxonomic names to taxonomic databases. | '''Problem:''' Scientific names are misspelled, resulting in poor matching of taxonomic names to taxonomic databases. | ||
'''Solution:''' | '''Solution:''' Avoid this issue with a pick list as data are entered. For a tutorial see [https://support.microsoft.com/en-us/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b Create a drop-down list] | ||
If data is already entered, check for non-standard values with this formula Where A1 is the Scientific name value being tested and the list to test against is on the taxa tab in cells A1 through A11. Any value in column A that does not match a value in the list to test against will result in #N/A. | |||
'''WARNING!''' Note the '''$''' in front of the letters and numbers that comprise the list to test against. Leaving these off may result in false errors as the test will run against blank cells if the formula is copied past row 11 (or whatever the last row number is in the list to test against), so make sure those $ are in place! | |||
=VLOOKUP(A1,'taxa'!$A$1:$A$11,1,FALSE) | |||
=== Unknown Higher Taxonomy === | === Unknown Higher Taxonomy === | ||