83
edits
Excel Data Quality Toolkit: Difference between revisions
Jump to navigation
Jump to search
→Non-standardized BasisOfRecord Values
Jegelewicz (talk | contribs) |
Jegelewicz (talk | contribs) |
||
| Line 214: | Line 214: | ||
If data is already entered, check for non-standard values with this formula Where A1 is the basisOfRecord value being tested and the list to test against is on the basis of record 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. | If data is already entered, check for non-standard values with this formula Where A1 is the basisOfRecord value being tested and the list to test against is on the basis of record 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,'basis of record'!**$**A$1:$A$11,1,FALSE) | =VLOOKUP(A1,'basis of record'!**$**A$1:$A$11,1,FALSE) | ||
'''Sample Results''' | |||
A | B | A | B | ||
| Line 243: | Line 243: | ||
Note that the unique values to check are in column A of the unique basis of record tab and the correct replacement values are in column B of the unique basis of record tab. Also note that the value being returned is from column B indicated as the 2nd column in the array. | Note that the unique values to check are in column A of the unique basis of record tab and the correct replacement values are in column B of the unique basis of record tab. Also note that the value being returned is from column B indicated as the 2nd column in the array. | ||
{|class="wikitable" | |||
|+ Sample Results | |||
A | |- | ||
!A !! B | |||
MaterialEntity | MaterialEntity | |- | ||
PreservedSpecimen | PreservedSpecimen | | MaterialEntity | MaterialEntity | ||
FossilSpecimen | FossilSpecimen | |- | ||
LivingSpecimen | LivingSpecimen | | PreservedSpecimen | PreservedSpecimen | ||
MaterialSample | MaterialSample | |- | ||
Preserved Specimen | PreservedSpecimen | | FossilSpecimen | FossilSpecimen | ||
HumanObservation | HumanObservation | |- | ||
MachineObservation | | | LivingSpecimen | LivingSpecimen | ||
fossil | FossilSpecimen | |- | ||
tissue | MaterialEntity | | MaterialSample | MaterialSample | ||
MaterialCitation | MaterialCitation | |- | ||
| Preserved Specimen | PreservedSpecimen | |||
|- | |||
| HumanObservation | HumanObservation | |||
|- | |||
| MachineObservation |MachineObservation | |||
|- | |||
| fossil | FossilSpecimen | |||
|- | |||
| tissue | MaterialEntity | |||
|- | |||
| MaterialCitation | MaterialCitation | |||
|} | |||