Hello,
I have ran into an issue with Excel 2003.
Problem: I am formulating Unique values from a column and counting
them on an Excel app.
Issue: I noticed that that the values of the count are sometimes off
by 1

I am searching through a column of values that contain both Letters
and Numbers.
I discovered that if there is letter that equals the same numeric
value (i.e. 5 and the letter E (Fifth character)) and there are two
values that are unique but contain that circumstance Excel see it as a
duplicate.

I am using this formula to find the distinct value:
=IF(OR(COUNTIF(CJ$1:CJ1,CG2:CG$210&"")=0),INDEX(CG 2:CG$210,MATCH
(0,COUNTIF(CJ$1:CJ1,CG2:CG$210&""),0)),"")
and this one to count that column:
=SUMPRODUCT(--(CJ2:CJ201<""))

Example:
E00X00838Q0
E00X0083AQ0
E00X00865E0 <--This one and
E00X0086EE0 <--This one are seen as duplicates
E00X00878E0
E00X00879E0

Is there a work around for this issue?

It works just fine for me.

E00X00865E0 <--This one and
E00X0086EE0 <--This one are seen as duplicates

Both of those entries are extracted as uniques.


--
Biff
Microsoft Excel MVP


wrote in message
...
Hello,
I have ran into an issue with Excel 2003.
Problem: I am formulating Unique values from a column and counting
them on an Excel app.
Issue: I noticed that that the values of the count are sometimes off
by 1

I am searching through a column of values that contain both Letters
and Numbers.
I discovered that if there is letter that equals the same numeric
value (i.e. 5 and the letter E (Fifth character)) and there are two
values that are unique but contain that circumstance Excel see it as a
duplicate.

I am using this formula to find the distinct value:
=IF(OR(COUNTIF(CJ$1:CJ1,CG2:CG$210&"")=0),INDEX(CG 2:CG$210,MATCH
(0,COUNTIF(CJ$1:CJ1,CG2:CG$210&""),0)),"")
and this one to count that column:
=SUMPRODUCT(--(CJ2:CJ201<""))

Example:
E00X00838Q0
E00X0083AQ0
E00X00865E0 <--This one and
E00X0086EE0 <--This one are seen as duplicates
E00X00878E0
E00X00879E0

Is there a work around for this issue?

On Dec 9, 1:25*pm, wrote:
Hello,
I have ran into an issue with Excel 2003.
Problem: *I am formulating Unique values from a column and counting
them on an Excel app.
Issue: *I noticed that that the values of the count are sometimes off
by 1

I am searching through a column of values that contain both Letters
and Numbers.
I discovered that if there is letter that equals the same numeric
value (i.e. 5 and the letter E (Fifth character)) and there are two
values that are unique but contain that circumstance Excel see it as a
duplicate.

I am using this formula to find the distinct value:
=IF(OR(COUNTIF(CJ$1:CJ1,CG2:CG$210&"")=0),INDEX(CG 2:CG$210,MATCH
(0,COUNTIF(CJ$1:CJ1,CG2:CG$210&""),0)),"")
and this one to count that column:
=SUMPRODUCT(--(CJ2:CJ201<""))

Example:
E00X00838Q0
E00X0083AQ0
E00X00865E0 <--This one and
E00X0086EE0 <--This one are seen as duplicates
E00X00878E0
E00X00879E0

Is there a work around for this issue?

Nevermind, I missed an issue. Stupid advanced sort got me. (trying
to resolve the problem caused a problem)