# Counting Duplicate Entries

May 21, 2007

I am trying to determine wether there are duplicate entries within a range and have then counted also. Example:

I have a list if zip codes and I want to find out how many of each zip code are there. If I have a list with:

12345
12345
12345
12346
12346
12347

I want to have it tell me there are 3 of "12345" and 2 of "12346", possibly next to the entry?

12345 3
12345
12345
12346 2
12346
12347

## Duplicate Entries

Sep 24, 2009

I have been trying a number of different functions!

I have the following countif function that is searching a worksheet (Cases Closed) for the name John in Column O and excluding Solutions in column x. The problem I have is there are duplicates cases in Column C that are being counted two and three times.

Is there anyway to have the following function exclude duplicates records in Column C? Just count unique records in Column C?

=(COUNTIF('Cases Closed'!O:O,"John"))-(COUNTIFS('Cases Closed'!O:O, "John", 'Cases Closed'!X:X, "*Solution*"))

## Counting Unique Entries ...

Nov 2, 2009

Couting Unique Entries

I've included a sample worksheet ...

## VBA Counting Used Entries In A Range

Jul 22, 2009

i need to count the number of used entries in a range.

so fex the range is A1:A10 but there are only 2 entries what i search is the number 2

is there a VBA function or so doing that.

ps i have this range.Rows.Count but this is counting me the length of the range even if there are no entries..

## Counting Entries Between Two Dates

Apr 29, 2009

Column A contains names
Column B their unique ID number
Column C the date a transaction occurred

How can I count the number of entries between two dates i.e. 1-31 April regardless of duplication or anything? I've tried a few things mentioned in other threads but none seem to work.

## Counting Entries Per Hour

Nov 16, 2007

My spreadsheet contains two text columns that represent dates and times; 11/21/2007 and 0935 for example. The rows are populated with the date and time for every event during a 48 hour period. A single worksheet may contain up to 3,000 events (rows).

How do I create a subtotal for the number of events in each of the forty-eight 1-hour periods?

## Counting Cell Entries ...

Aug 9, 2006

Colum A
Ticket No.
150258, 150268, 150269
150259
150249, 150266, 150271, 150279

Colum B
No. of Stillages
3
1
4

I have two colums. In A1:A4 are ticket numbers of products made. In B1:B4 are the number of stillages that the tickets relate to. What i am trying to work out, is a formula that will calculate the number of stillages produced based on the fact that each ticket number begins with "150". I have tried using COUNTIF, MATCH and FIND functions, but when i does work, will only return a value of 1.

## How To Combine Duplicate Entries

Apr 23, 2013

excel.jpg

Basically I've made this up myself because what ill be working with has 100s if not 1000s of rows with many different product numbers that's quantities are different. What I've been able to do up to now is sort the spreadsheet by the product number so all the same rows are next to each other. My problem is however I need a speedy way of making these duplicate rows become one but add the total quantity basically everything in the left screenshot into the one on the right. What I've tried up to now is sorting them so there together and manually adding them up and putting them into one of the rows quantity, then delete the rest. takes to long. Another was to make a row underneath the rows I need into one but that takes more time than manually adding and deleting the rest.

## Preventing Duplicate Entries

Jun 13, 2008

I'm working on a employee evaluation form. I'm using a Pass/Fail system.
I have a cell designated for PASS & one for FAIL. In these cells, I would put
"YES" if they passed, "YES" if they failed that section.
How can I prevent someone from mistakingly entering Yes in both cells?

## Clear Duplicate Entries

Jul 14, 2008

I am trying to find a macro that will clear duplicate entries in a column of data, and then move to the next column and repeat the process until it gets to a blank column.

There are blank cells between entries in a column. I need to keep the data unsorted as the cells are linked to others in the row, therefore I dont want to delete the cells, just clear the contents.

## Highlighting For Duplicate Entries

Nov 24, 2009

I would like to check column B and if any of these values match values in column J hightlight the cell on the same row as the match on column E green. I don't know much about excel but this would be a tremendous help because i'm looking through quite a bit of data. I've tried using conditional formatting and i can't seem to get that to work for my needs. Let me know if you need anymore information.

## Eliminate Duplicate Entries WITHIN A Row

Feb 8, 2010

I have a table of data like this:

A , 1 , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3 , 3

I need to eliminate duplicate entries WITHIN a row. So, I need the table to become:

A , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3

Is there an easy way to do this for a large sample? Also, there are actually more like 8 columns of this data, if thats important.

## Preventing Duplicate Entries

Dec 11, 2012

I've generated a sheet where, based on the total run hours of a generator set using a given fuel type, we can determine which engine to run on what fuel when. The problem I've run into is that if more than one engine has the same runtime, the chart returns just the lowest numbered engine and eliminates the remaining engine(s). What I'm trying to do is make it so that when this occurs, the suggested engines show up in numerical order. For example instead of 1, 1, and 1 showing up on the chart, something like 1, 2, and 5 will show when they all have 0 run hours. I've attached my chart below with some engines having the same runtimes. RunOrder.xlsx

## Prevent Duplicate Entries..

May 8, 2009

New here, I wanna do a "Prevent Duplicate Entries" into my column i try the following: http://www.mrexcel.com/articles/prev...duplicates.php

They all work well but if I'm using copy and paste It doesn't work! why is tat so?
Second issue will be i'm wanna do prevent duplication for the whole column not just one cell.

## Eliminate All Of The Duplicate Entries

Feb 5, 2009

I have a spreadsheet that has account numbers listed multiple times. I need to eliminate all of the duplicate entries...Is there a formula for this...?

## Stop Duplicate Entries In A Row

May 25, 2009

=COUNTIF(\$A\$1:\$D\$100,A1)=1
however I don't know how to change that to suit my needs (or even if it is at all possible to change it to suit my needs)

I will try my best to explain how my spreadsheet is set up as unfortunately I am at work and I can not use the method to show you the spreadsheet.

I already have Data Validation in use from a list that I created. I use the same validation list in Columns C, E, G, I, K-BA

I also use validation list in other cells, and it is OK if these are duplicated throughout the row.

The only cells that should not contain duplicate information are the ones from C, E, G, I, K-BA

Basically I want to stop someone accidentally chosing the same option from the list within the same ROW.

Just in case it makes any difference I should let you know that I have already had help from the forum regarding macros for this same spreadsheet, here is the macro that is currently running just in case it makes a difference if there is an answer to my current problem.

Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then

## Stop Duplicate Entries

Aug 24, 2006

I want to use (VBA) data vaildation to restrict duplicate entries.

Worksheet Name = "Metal Type"

NoDupe Cell = B15 (allowing text and numbers)

Range to check = (Sheet)="Metal Type" (Range)=A21:A100

Error = Message box "Enter Unique Name in Cell B15"

Then Exit Sub

## Count Of Duplicate Entries

Sep 14, 2006

there are unique entries like AU0896 etc. that are repeated in my list.
my job is to find how many unique entries there are and add the count at the end so, basically if there are 6 AU0896 entries, then I must create a AU08966 value.

## Duplicate Entries, Validate Only One

Jan 22, 2007

In my spreadsheet, on ( sheet A) I pick up a value to a range (O2:O22) from other worksheet (sheet B), the value is validated when a cell in the same range but other column (A2:A10) reach a specific criteria.

But in the same column if the criteria is typed again I gone a have the same value on range( O ), and I dont want that, because this value represents a total for a day, and is to be added to other cell.

I'm using this, to pick up the data
Ex:
(A2:A10) Criteria
(O2:O10) value picked up

on SheetA, Sumif(sheetBA10:A40,A2,SheetBK10:K40)

How can I count only one of the values picked up from sheet B?

## Find Duplicate Entries

Feb 14, 2007

Got a workbook with 4 worksheets and sometimes we need to cut and paste a row from one worksheet to another, now thats easy now as we are all human and sometimes it gets copied and we have duplicate logs.

Now each sheet has usually not got that many rows in it, but that spreadsheet is getting sent to the highest people in the company and any mistakes look poor.

What i'm looking for is a way that i can highligh any rows that a duplicated in each worksheet. I can do it in each worksheet using conditional formatting with something like = COUNTIF(\$A8:\$A\$20,A8)>1. but don't know how to check that is not on any other worksheet. On every worksheet only need to check Col A for the duplicate information

## Remove Duplicate Entries

Mar 7, 2007

Wihitn a large SS I have a column which has a number of entries that may include an amount of duplicate entries. Till now I have been using Sort and them manually deleting the duplicates is anyway that I can select just the Column and then without sorting go down and if a duplicate is found, then the duplicate would be deleted. Meant to say the dta is numeric.

## If Statement For Duplicate Entries

Mar 27, 2007

I have a spreadsheet which has data in column A & B. I trying to do an if statement to state "YES" if data in both A and B has been duplicated. EG 111 in (A) & 222 in (B) are a PAIR, im need an if statemnet in Column (C) to say if this PAIR are duplicated in these two columns return "YES". I have tried THREE different methods to do acheive this which are in the attached document. The main issue I am having is the iff statement is returning yes for some of the number when thye are completely different. I have used concatenate, & and tried to multiply the two values to try to make it unique. I have an example of each in the separate tabs.

## Counting Entries In Date Range?

Feb 12, 2014

I'm looking for a way to count entries of numbers per day of the week within a date range. Example

Start End Mo Tu We Th Fr Sa Su TOTAL
2/10 2/23 1 1 1 1 0 1 1 12
3/1 3/31 1 2 1 1 1 1 2 40

I don't know if that possible in excel or its to much

## Counting Unique Entries From 3 Workbooks

Sep 23, 2009

I need to count the number of unique names (column A) on 3 separate worksheets. Each worksheet represents a facility that people visit. It is possible that the same person will visit all three facilities. If this is the case I want to only count them once, even though they may have numerous entries on each.

I need to get the same result as if I was to copy and paste all of the names from three spreadsheets on to one (which isnt an option) and then count unique names. To do this on one sheet I have been using =sumproduct((A1:A100<>"")/countif(A1:a100,A1:A100))

## Counting Entries Based On Month

Oct 21, 2009

I have a column of dates and want to count how many times there is an entry for a particular month. example:

(column D)
7/4/2009
7/13/2009
7/22/2009
8/1/2009
8/6/2009
9/15/2009

I want to be able to coun (countif?) how many times there is a July listing, August listing, etc.

=COUNTIF(D3:D20,">=7/1/2009")+COUNTIF(D3:D20,"<=7/31/2009")
=SUMPRODUCT(--(D3:D20>="7/1/2009")*(D3:D20<="7/31/2009"))

## Counting Entries If Adjacent Box Matches

Sep 17, 2013

Im trying to make an excel spreadsheet for work for working out how many of each product and individual staff member has sold

I have 2 columns one with staff and one with what they sold e.g.

Staff Member - Sales
Staff 1 - prod A
Staff 2 - prod B
Staff 3 - prod A
Staff 2 - prod C
Staff 1 - prod A

and have another table, the one that i want to populate, that looks like this

Staff 1
prod A =
prod B =
prod C =

Staff 2
prod A =
prod B =
prod C =

etc

I cant seem to find a function that will work out how many of prod A staff 1 has sold and how many prod B he has sold etc

i have tried things like countif but i cant seem to get them to link the staff member and sales boxes together, the only way i can think of is to write down an equation for each line, but i have hundreds of sales and that would takes ages

## Counting Data Entries In Rows

Apr 11, 2007

I run an online golf tour and I need a little help with the coding of my handicap excel sheet. (For any golfers out there this is a custom handicap system, not the R&A version).

First I'll explain what I have, then my problem.

I have from left to right the following, Name, Scores, Worst Score, 2nd Worst Score, Average, Hcp. (There are other columns but are not important here)

The Scores columns record each round played, so at the moment we only have 10 columns with data as its the start of the season. I currently find the "Worst Scores" from the 10 columns, as they are all relevant at the moment. My problem will arise when I get over 12 columns of data. From that point on I need to find the "HIGHEST 10" from the "LAST 12" entries.

Easy you might say, BUT the last 12 rounds played could be spread over 100+ columns and every row could be different.

Example, Player 1 could have scores in column A to AB and then in AD to AE. Where as Player 2 could have scores in every alternate column.

I'm assuming I'll have to set up some form of count to count the cells that have data in them until I get to 12. Then extract the ones I dont need.

My question is, How do I find the last 12 column cells used in any row AND extract the highest 2 from those 12?

## Counting Entries Using Time Criteria

Jun 6, 2006

I need to generate a report that indicates how long it took a vendor to respond, and indicate if the time was less than 2 hours.

I'm having trouble with a formula that will indicate how long it took for the vendor to respond during business hours (8-5 monday through friday). I can easily calculate the total elapsed time, but I'm at a loss on how to account for the after hours time period.

Would this be better handled with VBA?

## Counting Entries In A Validation Field

Apr 17, 2007

I need to count entries in a validation field

I tried using =COUNT(3,E3,M3,U3,AC3) but this does not work.

## Counting Number Of Multiple Entries

Jun 6, 2007

I am trying to count the number of indentical entries in my spreadsheet. I got the conditional formatting to color indentical entries that have more than 2 entries. I need to know how multiple entries are in my spreadsheet. I am using =COUNTIF(\$A\$1:\$A\$10000,A1)>2.