Count occurance of largest duplicate number in a single column ran
 

I need to count the occurance of only the largest duplicate number in a range
and return "TIE" in cells adjacent to those cells that have the largest
duplicate number.
Example
A B
1 ""
2 ""
5 TIE
3 ""
5 TIE
5 TIE

COUNTIF provides desired output but it's output provides "TIE" for all
matching cells without respect to the largest number.

Thanks,

Count occurance of largest duplicate number in a single column ran
 

Let's see if I understand what you want correctly.....

With
A1:A6 containing your posted data

You want to determine the largest value that is duplicated and flag cells
that match that value as "TIE"....even if another single occurring value is
larger.

If that is correct then
B1: =IF(A1=MAX(INDEX((COUNTIF($A$1:$A$6,$A$1:$A$6)1)* $A$1:$A$6,0)),"TIE","")

Copy that formula down through B6

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"catpro" wrote:

I need to count the occurance of only the largest duplicate number in a range
and return "TIE" in cells adjacent to those cells that have the largest
duplicate number.
Example
A B
1 ""
2 ""
5 TIE
3 ""
5 TIE
5 TIE

COUNTIF provides desired output but it's output provides "TIE" for all
matching cells without respect to the largest number.

Thanks,

Count occurance of largest duplicate number in a single column
 

The formula works as you configured it to but, I only wish to output "TIE"
for the duplicates of the largest number in the range.

Thanks for your help,

Catpro

"Ron Coderre" wrote:

Let's see if I understand what you want correctly.....

With
A1:A6 containing your posted data

You want to determine the largest value that is duplicated and flag cells
that match that value as "TIE"....even if another single occurring value is
larger.

If that is correct then
B1: =IF(A1=MAX(INDEX((COUNTIF($A$1:$A$6,$A$1:$A$6)1)* $A$1:$A$6,0)),"TIE","")

Copy that formula down through B6

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"catpro" wrote:

I need to count the occurance of only the largest duplicate number in a range
and return "TIE" in cells adjacent to those cells that have the largest
duplicate number.
Example
A B
1 ""
2 ""
5 TIE
3 ""
5 TIE
5 TIE

COUNTIF provides desired output but it's output provides "TIE" for all
matching cells without respect to the largest number.

Thanks,

Count occurance of largest duplicate number in a single column
 

So if the largest number has no duplicates, you just want "". You are not
looking for the largest duplicated value, just the largest number and, if it
happens to be duplicated, show "TIE"?

If your data is in A1:A12, one possibility:
=IF((COUNTIF(A$1:A$12,MAX(A$1:A$12))1)*(A1=MAX(A$ 1:A$12)),"TIE","")


"catpro" wrote:

The formula works as you configured it to but, I only wish to output "TIE"
for the duplicates of the largest number in the range.

Thanks for your help,

Catpro

"Ron Coderre" wrote:

Let's see if I understand what you want correctly.....

With
A1:A6 containing your posted data

You want to determine the largest value that is duplicated and flag cells
that match that value as "TIE"....even if another single occurring value is
larger.

If that is correct then
B1: =IF(A1=MAX(INDEX((COUNTIF($A$1:$A$6,$A$1:$A$6)1)* $A$1:$A$6,0)),"TIE","")

Copy that formula down through B6

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"catpro" wrote:

I need to count the occurance of only the largest duplicate number in a range
and return "TIE" in cells adjacent to those cells that have the largest
duplicate number.
Example
A B
1 ""
2 ""
5 TIE
3 ""
5 TIE
5 TIE

COUNTIF provides desired output but it's output provides "TIE" for all
matching cells without respect to the largest number.

Thanks,

Count occurance of largest duplicate number in a single column
 

Ron's formula produces the results you posted in your example. Isn't that
what you wanted?

Or, is this what you want:

A B
1 ""
2 ""
5 ""
3 ""
5 TIE
5 TIE

In my "book" the first instance of a value isn't a duplicate. The duplicate
is the next instance (if it exists) and every instance after that.

Entered in B1 and copied down:

=IF(AND(A1=MAX(A$1:A$6),COUNTIF(A$1:A1,A1)1),"Tie ","")

Biff

"catpro" wrote in message
...
The formula works as you configured it to but, I only wish to output "TIE"
for the duplicates of the largest number in the range.

Thanks for your help,

Catpro

"Ron Coderre" wrote:

Let's see if I understand what you want correctly.....

With
A1:A6 containing your posted data

You want to determine the largest value that is duplicated and flag cells
that match that value as "TIE"....even if another single occurring value
is
larger.

If that is correct then
B1:
=IF(A1=MAX(INDEX((COUNTIF($A$1:$A$6,$A$1:$A$6)1)* $A$1:$A$6,0)),"TIE","")

Copy that formula down through B6

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"catpro" wrote:

I need to count the occurance of only the largest duplicate number in a
range
and return "TIE" in cells adjacent to those cells that have the largest
duplicate number.
Example
A B
1 ""
2 ""
5 TIE
3 ""
5 TIE
5 TIE

COUNTIF provides desired output but it's output provides "TIE" for all
matching cells without respect to the largest number.

Thanks,

Count occurance of largest duplicate number in a single column ran
 

=IF(A1=MAX(($A$1:$A$6)*(COUNTIF($A$1:$A$6,$A$1:$A$ 6)1)),"TIE","")


"catpro" wrote:

I need to count the occurance of only the largest duplicate number in a range
and return "TIE" in cells adjacent to those cells that have the largest
duplicate number.
Example
A B
1 ""
2 ""
5 TIE
3 ""
5 TIE
5 TIE

COUNTIF provides desired output but it's output provides "TIE" for all
matching cells without respect to the largest number.

Thanks,