Showing posts with label Unique List. Show all posts
Showing posts with label Unique List. Show all posts

FIND THE UNIQUE AND DUPLICATE NAMES

FIND THE UNIQUE AND DUPLICATE NAMES


QUESTION

Find the duplicate and unique Names in the SalesRep Column
Your answer should return Duplicate if the name exists more than 1 time other unique

SOLUTION

Use COUNTIF Formula/Function to get the frequency of every SalesRep & Use if Condition for Conditional answer.

USE FUNCTION IN VIDEO

=IF(COUNTIF($D$5:$D$34,D5)>1,"Duplicate","Unique") 


 

Dailymotion




COUNTIF FUNCTION

 =COUNTIF(Range, Criteria)

RANGE

The range of cells to count.

CRITERIA

The Criteria thats Control which cells should be counted

IF FUNCTION

=IF(logical_test, value_if_true, [value_if_false])

LOGICAL_TEST (Required)

The condition you want to test.

VALUE_IF_TRUE (Required)

The value that you want returned if the result of logical_test is TRUE.
VALUE_IF_FALSE (Optional)

The value that you want returned if the result of logical_test is FALSE.  

                       SAMPLE FILE DOWNLOAD CLICK

 

Unique List With Data Validation

Unique List With Data Validation www.coursedots.com

Data Validation List Without Duplicate


When you create a formatted Excel Table, you can use data validation to prevent within a column. In this Video, Unique list is entered in the column B, and each value much be unique. 




ADD THE DATA VALIDATION:



  • Select cells B:B
  • On the Ribbon's Data tab, Click data validation
  • On the settings tab from the Allow drop down, choose:Custom

  • NOTE if there could be blank cells in the column, remove the check mark from "Ignore Blank Cells", or duplicates will be allowed
  • In the formula box, you'll use the countif function to check value being entered in cell B2. It will be compared to all the entries in the Unique list column. To Prevent duplicates, the count must be 1 Or 0  "=countif($B:$B,B1)<2"