But what happens if thew dupes are not in adjacent cells?Jumping Frog wrote:It depends on how you want to count the duplicates.Charles L. Cotton wrote: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.
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
Gets a little more complicated.