Duplicate Error!!!!!
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? |
Duplicate Error!!!!!
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? |
Duplicate Error!!!!!
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) |
All times are GMT +1. The time now is 10:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com