Finding duplicates using a margin of error

%3CLINGO-SUB%20id%3D%22lingo-sub-1417738%22%20slang%3D%22en-US%22%3EFinding%20duplicates%20using%20a%20margin%20of%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1417738%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20add%20a%20field%20validation%20to%20my%20excel%20spreadsheet%20that%20allows%20me%20not%20only%20to%20identify%20duplicate%20values%20that%20are%20identical%20(which%20I%20know%20how%20to%20do)%20but%20that%20will%20%3CU%3Esignal%20fields%20that%20are%20in%20the%20same%20range%20of%20values%3C%2FU%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20example%20%3A%20I%20have%20a%20variable%20that%20is%20the%20average%20of%20two%20forearm%20length%20measures%2C%20and%20I%20would%20like%20to%20signal%20values%20that%20are%20within%20%2B%2F-%201%20cm%20of%20each%20other%20to%20account%20for%20measuring%20errors.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20how%20I%20can%20achieve%20this%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20Morgana%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1417738%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1418045%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20duplicates%20using%20a%20margin%20of%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1418045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F679855%22%20target%3D%22_blank%22%3E%40morgana9%3C%2FA%3E%26nbsp%3BCould%20you%20use%20a%20conditional%20formatting%20formula%3F%3C%2FP%3E%3CP%3EThis%20formula%20would%20flag%20anything%20with%20a%20variance%20of%201%20to%20a%20specified%20variable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20thinking%20something%20like%3A%3C%2FP%3E%3CP%3E%3DAND(D1%20%26lt%3B%3D%20%24G%241%2B1%2CD1%26gt%3B%3D%24G%241-1)%3C%2FP%3E%3CP%3E%5Bwhere%20the%20variable%20is%20in%20G1%2C%20and%20the%20range%20of%20cells%20to%20validate%20against%20it%20is%20in%20column%20D%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1420481%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20duplicates%20using%20a%20margin%20of%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1420481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462348%22%20target%3D%22_blank%22%3E%40macrordinary%3C%2FA%3E%26nbsp%3BI'm%20not%20sure%20if%20that's%20exactly%20what%20I'm%20trying%20to%20achieve%20as%20it's%20not%20a%20specified%20value%20that%20I'd%20like%20to%20validate%20the%20range%20of%20cells%20against%2C%20but%20each%20other%20(signaling%20duplicates%20but%20with%20a%20little%20variability%20allowed)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20thanks%20anyways!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424456%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20duplicates%20using%20a%20margin%20of%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424456%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462348%22%20target%3D%22_blank%22%3E%40macrordinary%3C%2FA%3E%26nbsp%3Blittle%20update%2C%20I%20did%20end%20up%20using%20a%20little%20variation%20of%20your%20formula%20and%20making%20it%20work%20for%20my%20project%20needs.%20so%20thank%20you%20very%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424634%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20duplicates%20using%20a%20margin%20of%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424634%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F679855%22%20target%3D%22_blank%22%3E%40morgana9%3C%2FA%3E%26nbsp%3BGlad%20I%20could%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi everyone!

 

I am trying to add a field validation to my excel spreadsheet that allows me not only to identify duplicate values that are identical (which I know how to do) but that will signal fields that are in the same range of values,

 

for example : I have a variable that is the average of two forearm length measures, and I would like to signal values that are within +/- 1 cm of each other to account for measuring errors. 

 

Does anyone know how I can achieve this ?

 

Thanks! Morgana

4 Replies
Highlighted

@morgana9 Could you use a conditional formatting formula?

This formula would flag anything with a variance of 1 to a specified variable.

 

I'm thinking something like:

=AND(D1 <= $G$1+1,D1>=$G$1-1)

[where the variable is in G1, and the range of cells to validate against it is in column D]

Highlighted

@macrordinary I'm not sure if that's exactly what I'm trying to achieve as it's not a specified value that I'd like to validate the range of cells against, but each other (signaling duplicates but with a little variability allowed)

 

but thanks anyways!

Highlighted

@macrordinary little update, I did end up using a little variation of your formula and making it work for my project needs. so thank you very very much!

Highlighted

@morgana9 Glad I could help!