» Checking for Duplicate Values within a Range
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Problem: Columns A & B contain two lists of values.
We want to create a formula that will check whether there is any duplication of values within either list (blank cells are to be ignored).
Solution:
Use the IF, COUNTA, SUMPRODUCT, and COUNTIF functions as shown in the following formula:
=IF(COUNTA(A2:A7)=SUMPRODUCT((A2:A7<>" ")/COUNTIF(A2:A7,A2:A7&"")),"No Duplicates","Duplicates")
The formula will return "Duplicates" if the list contains duplicate values, otherwise it will return "No Duplicates".
Book Store:
Recommended Books:
- The Analysis and Use of Financial Statements
- Special Edition Using Microsoft Word 2002
- Word 2002: The Complete Reference
- Finance and Accounting for Nonfinancial Managers
- Quantitative Methods in Derivatives Pricing: An Introduction to Computational Finance
- Keys to Reading an Annual Report (Barron's Business Keys)
No comments have been submitted.
