Excel Question re: counting duplicate entries

[Charles L. Cotton]

I need to be able to count the total number of duplicate entries in a table. I know how to do this with a pivot table, but that won't work for our purpose. We need to be able to determine the total number of duplicate entries so we can get a net number and compare that to our Club database. We don't care who has a duplicate entry. The key field for determining duplicates would be a member number.

Thanks,
Chas.

[Keith B]

See if this does what you need http://office.microsoft.com/en-us/excel ... 1&client=1" onclick="window.open(this.href);return false;

[Charles L. Cotton]

See if this does what you need http://office.microsoft.com/en-us/excel ... 1&client=1" onclick="window.open(this.href);return false;

Thanks Keith, that gives me a quick way to check duplicate Members. I also created a macro copying the entire sheet to a new tab. Then duplicates are deleted and a count function is used to get me a number I can make available to our Membership Director.

In case anyone is wondering, no we don't have a membership records on an Excel spreadsheet. This is for something else within the Club.

Thanks again,
Chas.

[RogueUSMC]

If the duplicates you are truing to find are all on the same column, just select the column and use the conditional formatting to change the color, font, size, weight, etc for anything that has a duplicate. You can leave the conditional formatting intact, then as you enter data, it will highlight duplicates live.

[Charles L. Cotton]

If the duplicates you are truing to find are all on the same column, just select the column and use the conditional formatting to change the color, font, size, weight, etc for anything that has a duplicate. You can leave the conditional formatting intact, then as you enter data, it will highlight duplicates live.

Thanks, but I don't need to identify the duplicates, I need a count of the duplicates. This is so we can get a net figure for Members who have accomplished a specified task.

Chas.

[Jumping Frog]

Thanks, but I don't need to identify the duplicates, I need a count of the duplicates. This is so we can get a net figure for Members who have accomplished a specified task.

It depends on how you want to count the duplicates.

Assume you have the following names in Column A:

A1-Name
A2-Bob
A3-Charles
A4-Charles
A5-David

If the first instance of "Charles" does not count as a duplicate, but only the second "Charles" duplicate entry is counted, then create a column with the next formula, and then copy it down it's own column:

=if(a3=a2,1,0)

Now sum that column, and your count will be equal to 1.

However, if you want to count both instances of "Charles" as a duplicate, so that your total for both = 2, then create a column with the next formula, and then copy it down it's own column:

=if(or(a2=a1,a2=a3),1,0)

Now sum that column

[jimlongley]

Thanks, but I don't need to identify the duplicates, I need a count of the duplicates. This is so we can get a net figure for Members who have accomplished a specified task.

It depends on how you want to count the duplicates.

Assume you have the following names in Column A:

A1-Name
A2-Bob
A3-Charles
A4-Charles
A5-David

If the first instance of "Charles" does not count as a duplicate, but only the second "Charles" duplicate entry is counted, then create a column with the next formula, and then copy it down it's own column:

=if(a3=a2,1,0)

Now sum that column, and your count will be equal to 1.

However, if you want to count both instances of "Charles" as a duplicate, so that your total for both = 2, then create a column with the next formula, and then copy it down it's own column:

=if(or(a2=a1,a2=a3),1,0)

Now sum that column

But what happens if thew dupes are not in adjacent cells?

Gets a little more complicated.

[Jumping Frog]

Yes, the assumption made is the cells of interest are sorted.

[RogueUSMC]

Using the filters, give each 'task' an identifier listing all tasks in one column, you can cull your list by task, member, date, etc. six ways to Sunday and leave your data intact. I do this with our warehouse inventory. I can filter down to a certain SKU and tell you how many we have gotten, how many we have sent out...I can even tell you what purchase order they were received on with duplicate lines for different dates, then refilter by that purchase order and date to see what was on the truck with it...all without even resorting the data itself.

[Charles L. Cotton]

Okay, I guess I was being a little too cryptic. To renew your PSC Membership, one must watch a safety video and pass a related exam. All of this is automated and Members' scores are in a database. I export this data to Excel for analysis and to upload to a different database/website.

If a person fails the exam and then passes later, there are two entries for the Member making it impossible to quickly identify how many of our Members have yet to pass the safety exam. Some Members pass the exam but take it again, apparently trying to get a perfect score.

I solved the problem with a macro, but I was hoping to use one that was a bit more efficient.

Thanks,
Chas.