Identifying Duplicate Entries

[jroberts]

Hi,

I'm creating a spreadsheet that is basically just tallying votes for separate categories.

What I'm trying to do is set up a formula that will take a range of names and just display the name that is duplicated the most (aka: has the most votes).

I've read through most of the posts about duplicate entries, but still couldn't figure out how to change the formulas to accommodate what I'm trying to accomplish.

Any help at all would be greatly appreciated.

The sheet is set up like this:

Column A Column B Column C Column D
Best Man steve john steve
Best Woman mary martha martha
Best Person steve steve martha

I'm hoping to get Column E set up to display only the name that is duplicated the most.

[Pharm_hand]

Easiest way to do this is to make a pivot table. The list is the data source. For column, use the count of "Name" The pivot will report that Steve has 5 votes, Sally has 9 etc. You can extend the list endlessly, and click refresh on the pivot and it will update the voting. Look out for the hanging chads!

[NBVC]

Try:

=INDEX($B2:$D2,MATCH(MAX(COUNTIF($B2:$D2,$B2:$D2)),COUNTIF($B2:$D2,$B2:$D2),0))

confirmed with CTRL+SHIfT+ENTER not just ENTER

Then copy down....


if you adjust ranges, you must reconfirm with CSE key combo.

[jroberts]

This has been very helpful, thanks for everything!

[jroberts]

Ok, that formula works perfectly for what I wanted to do.

But, I've run into another problem...if there is a tie, it puts whichever name appears first alphabetically. With entries into the 100s, it would take a long time trying to find the tied name.

Any ideas on how to display not only if 2 names appear the same amount of times, but what names they are?

Thanks!

[NBVC]

You can do this by using some helper rows.

Assuming the same setup as your original post: i.e. names begin in B2

In a row below the entire list enter formula in column 2:

=IF(COUNTIF($B2:$F2,B2)=MAX(COUNTIF($B2:$F2,$B2:$F2)),IF(COUNTIF($A3:A3,B2)>0,"",B2),"")

adjust range to include all columns in your range and then confirm with CTRL+SHIFT+ENTER and copy it down as many rows as you have rows of data and then copy those to the right as far as necessary to cover all columns of data.

This will show all names that are repeated the most number of times.


If you want to relist the names so there are no blanks..

enter this formula in another row:

=IF(COLUMNS($A$1:A$1)>COUNTIF($B5:$F5,"?*"),"",INDEX($B5:$F5,SMALL(IF($B5:$F5<>"",COLUMN($B5:$F5)-COLUMN($B$5)+1),COLUMNS($A$1:A$1))))

The B5:F5 in this formula must be changed to reference the range of the first row of data you created with the previous formula in this post. Confirm the formula with CTRL+SHIFT+ENTER and copy it down as far as necessary and to the right as far as you think there can be repeats.

Hope this helps....

I attached a sample file....