Using Excel 2003. I have a ws with 3000+ rows of data that I need to find at
least the first instance of a duplicate. I copied and modified the following
formula from http://www.cpearson.com/excel/Duplicates.aspx, however I am
getting incorrect results:
=IF(COUNTIF($A$1:$A$3500,A1)=1,FALSE,COUNTIF($A$1: A1,A1)=1)

In some cases, the fomula gives the correct results, other areas it will
give the result of "True" although the entry is unique, other areas the
result is "False" and there are at least 3 entries in the column. Following
are samples of the results:

This section the results are OK:
Column A B C D
4536057761268403 1 50000 TRUE
4536057761268403 1 40000 FALSE
4536057781990705 1 30000 FALSE
4536057811016703 1 50000 TRUE
4536057811016703 1 40000 FALSE
4536057811645204 1 50000 TRUE
4536057811645204 1 40000 FALSE
4536057811645204 1 30000 FALSE

This section I get True on a unique entry:
5047429900014284707 21 112800 FALSE
5047429900014292510 1 30000 TRUE
5047429900014294003 21 122000 FALSE

This section I get False on a multiple:
5047429900014294904 1 42000 FALSE
5047429900014294920 21 120000 FALSE
5047429900014294920 21 35000 FALSE
5047429900014294920 21 115500 FALSE
5047429900014295273 1 48200 FALSE

Am I using the formula incorrectly, or is there simply too much data to
compare that it doesn't work on every row? The results, correct and
incorrect, are mixed throught the worksheet. All numbers in Column A are
formatted as 'Text'.

Any help would be appreciated, I have a large document I am trying to
finalize.
Thanks!
--
Linda

The formula only compares cells in column A and the example shows correct
results.
The second occurence does not equal 1, it equals 2 and is therefor, false.
The one with true would be the actionable item.


"L.Mathe" wrote in message
...
Using Excel 2003. I have a ws with 3000+ rows of data that I need to find
at
least the first instance of a duplicate. I copied and modified the
following
formula from http://www.cpearson.com/excel/Duplicates.aspx, however I am
getting incorrect results:
=IF(COUNTIF($A$1:$A$3500,A1)=1,FALSE,COUNTIF($A$1: A1,A1)=1)

In some cases, the fomula gives the correct results, other areas it will
give the result of "True" although the entry is unique, other areas the
result is "False" and there are at least 3 entries in the column.
Following
are samples of the results:

This section the results are OK:
Column A B C D
4536057761268403 1 50000 TRUE
4536057761268403 1 40000 FALSE
4536057781990705 1 30000 FALSE
4536057811016703 1 50000 TRUE
4536057811016703 1 40000 FALSE
4536057811645204 1 50000 TRUE
4536057811645204 1 40000 FALSE
4536057811645204 1 30000 FALSE

This section I get True on a unique entry:
5047429900014284707 21 112800 FALSE
5047429900014292510 1 30000 TRUE
5047429900014294003 21 122000 FALSE

This section I get False on a multiple:
5047429900014294904 1 42000 FALSE
5047429900014294920 21 120000 FALSE
5047429900014294920 21 35000 FALSE
5047429900014294920 21 115500 FALSE
5047429900014295273 1 48200 FALSE

Am I using the formula incorrectly, or is there simply too much data to
compare that it doesn't work on every row? The results, correct and
incorrect, are mixed throught the worksheet. All numbers in Column A are
formatted as 'Text'.

Any help would be appreciated, I have a large document I am trying to
finalize.
Thanks!
--
Linda

It is also possible where you are getting apparent errors on unique entries
that if the numbers are truncated or rounded that the result is based on
the actual value which VBA will read but may not be visible to the ;user. I
had trouble getting the numbers to post into a worksheet.

If you go back and read Chips description of what the code does, you will
see that he was using the code in conditional formatting. He has other code
at the top of the page that might be more useful for your purposes.


"L.Mathe" wrote in message
...
Using Excel 2003. I have a ws with 3000+ rows of data that I need to find
at
least the first instance of a duplicate. I copied and modified the
following
formula from http://www.cpearson.com/excel/Duplicates.aspx, however I am
getting incorrect results:
=IF(COUNTIF($A$1:$A$3500,A1)=1,FALSE,COUNTIF($A$1: A1,A1)=1)

In some cases, the fomula gives the correct results, other areas it will
give the result of "True" although the entry is unique, other areas the
result is "False" and there are at least 3 entries in the column.
Following
are samples of the results:

This section the results are OK:
Column A B C D
4536057761268403 1 50000 TRUE
4536057761268403 1 40000 FALSE
4536057781990705 1 30000 FALSE
4536057811016703 1 50000 TRUE
4536057811016703 1 40000 FALSE
4536057811645204 1 50000 TRUE
4536057811645204 1 40000 FALSE
4536057811645204 1 30000 FALSE

This section I get True on a unique entry:
5047429900014284707 21 112800 FALSE
5047429900014292510 1 30000 TRUE
5047429900014294003 21 122000 FALSE

This section I get False on a multiple:
5047429900014294904 1 42000 FALSE
5047429900014294920 21 120000 FALSE
5047429900014294920 21 35000 FALSE
5047429900014294920 21 115500 FALSE
5047429900014295273 1 48200 FALSE

Am I using the formula incorrectly, or is there simply too much data to
compare that it doesn't work on every row? The results, correct and
incorrect, are mixed throught the worksheet. All numbers in Column A are
formatted as 'Text'.

Any help would be appreciated, I have a large document I am trying to
finalize.
Thanks!
--
Linda

Thanks for your input. I knew the formula was for conditional formatting, it
seems to work as well for True or False response. I also tried the other
formula in the website, and unfortunately, still had an incorrect result. I
even tried removing Col. B & C as I really don't need that part of the data
any longer, then I manually input the formula, copied and pasted down, still
didn't work. You may be right that the data is truncated when it is pulled
in using VBA, but not visible to the user.

I think I'll stick with VBA that loops through the data to code duplicates.
It takes a little longer to run, I'll live with it.

Thanks again for taking the time to respond.
--
Linda


"JLGWhiz" wrote:

It is also possible where you are getting apparent errors on unique entries
that if the numbers are truncated or rounded that the result is based on
the actual value which VBA will read but may not be visible to the ;user. I
had trouble getting the numbers to post into a worksheet.

If you go back and read Chips description of what the code does, you will
see that he was using the code in conditional formatting. He has other code
at the top of the page that might be more useful for your purposes.


"L.Mathe" wrote in message
...
Using Excel 2003. I have a ws with 3000+ rows of data that I need to find
at
least the first instance of a duplicate. I copied and modified the
following
formula from http://www.cpearson.com/excel/Duplicates.aspx, however I am
getting incorrect results:
=IF(COUNTIF($A$1:$A$3500,A1)=1,FALSE,COUNTIF($A$1: A1,A1)=1)

In some cases, the fomula gives the correct results, other areas it will
give the result of "True" although the entry is unique, other areas the
result is "False" and there are at least 3 entries in the column.
Following
are samples of the results:

This section the results are OK:
Column A B C D
4536057761268403 1 50000 TRUE
4536057761268403 1 40000 FALSE
4536057781990705 1 30000 FALSE
4536057811016703 1 50000 TRUE
4536057811016703 1 40000 FALSE
4536057811645204 1 50000 TRUE
4536057811645204 1 40000 FALSE
4536057811645204 1 30000 FALSE

This section I get True on a unique entry:
5047429900014284707 21 112800 FALSE
5047429900014292510 1 30000 TRUE
5047429900014294003 21 122000 FALSE

This section I get False on a multiple:
5047429900014294904 1 42000 FALSE
5047429900014294920 21 120000 FALSE
5047429900014294920 21 35000 FALSE
5047429900014294920 21 115500 FALSE
5047429900014295273 1 48200 FALSE

Am I using the formula incorrectly, or is there simply too much data to
compare that it doesn't work on every row? The results, correct and
incorrect, are mixed throught the worksheet. All numbers in Column A are
formatted as 'Text'.

Any help would be appreciated, I have a large document I am trying to
finalize.
Thanks!
--
Linda


.