Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Formula for showing repeated values

0

Excel Formula for showing repeated values

Answer
Discuss

Discussion

Hi, I have a similar problem, hope you could assist. I'm trying to calculate agent repeat call. I want to find out how many times someone picked the same call in a 24 hour period. what formula can I use? I'm looking at 7k records on averageĀ 
Sam Lee (rep: 2) Jul 12, '19 at 2:04 am
Add to Discussion

Answers

0

Let's say, all your values are in the range $A$2:$A$1000. In fact, this range is chosen larger than your actual data so that you don't need to adjust it whenever a couple of new rows are added. Your actual data might only have 500 rows, like that.

The following formula will count how many times the value in A2 is found in A2:A1000.

=COUNTIF($A$2:$A$1000,$A2)

You can copy that formula down all the way to row 500 or 1000. That will put a lot of zeroes in the rows where column A is blank. Therefore you modify the above. The formula below will display a blank cell if there is no value in column A.

=if($A2="","",COUNTIF($A$2:$A$1000,$A2))

However, you aren't really interested in the count. You only want to show how many duplicates there are. For that you modify the formula further, like this.

=IF($A2="","",COUNTIF($A$2:$A$1000,$A2)-1)

But perhaps you don't want to know how many duplicates there are. Your flexible question allows for the possiblity that you merely what to know whether a particular value is unique in the list. The formula below would provide that information.

=IF($A2="","",IF(COUNTIF($A$2:$A$1000,$A2)>1,"There is at least one duplicate","This value is unique"))

You can modify the text between quotation marks to suit your preferences.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login