I am entering data to column B, a 5-digit number. Is there a formula
that I can put in column D that will tell me if the number is a
duplicate. The numbers batch numbers from the accounting system.
There should be only one. But since I am entering the batchs with them
unsorted, I was curious if a formula could check.

TK

TK, someone will no doubt post a formula I would imagine. I might mention
that the ASAP Utility that is free to download has a "count duplicates in
selection" where it will color code any dupes. It is under the ASAP
"Information" heading. Google for ASAP Utility and take a look. I have no
affiliation with them, but I use it for a number of interesting little
utilities like this.

If you need a permanent column for your batch data, I guess you'd need to
solve it formulaically. But...

Cheers! Brad (in Texas)


"TKnTexas" wrote in message
oups.com...
I am entering data to column B, a 5-digit number. Is there a formula
that I can put in column D that will tell me if the number is a
duplicate. The numbers batch numbers from the accounting system.
There should be only one. But since I am entering the batchs with them
unsorted, I was curious if a formula could check.

TK

This formula should do the trick:

=IF(COUNTIF(B:B,B1)1,"Dupe","")

HTH,
Elkar


"TKnTexas" wrote:

I am entering data to column B, a 5-digit number. Is there a formula
that I can put in column D that will tell me if the number is a
duplicate. The numbers batch numbers from the accounting system.
There should be only one. But since I am entering the batchs with them
unsorted, I was curious if a formula could check.

TK

In .com,
TKnTexas spake thusly:

I am entering data to column B, a 5-digit number. Is there a
formula that I can put in column D that will tell me if the
number is a duplicate.

Supposing your data starts in Row 2, and supposing your
data ends in Row 2000, then in D2 and dragged down could
go:

=COUNTIF($B$2:$B$2000,$B2)

Here's a sample:

B C D
----- --- ---
12345 1
12346 1
12347 1
12348 1
12349 1
12350 1
12351 1
12352 1
12353 2 <------
12353 2 <------
12355 1


You could also, or alternatively, use Conditional Formatting
(Format - Conditional Formatting) to bring dupes to your
attention. The conditional formula could be, e.g.:

=COUNTIF($B$2:$B$2000,$B2)-1

-dman-

Thanks for both responses. I am used to using a database, usually
Filemaker, to do this because I can put in the validations I need
easier than in Excel. But I am doing this at a temp job so I have to
use the tools on hand.
TK

Dallman Ross wrote:
In .com,
TKnTexas spake thusly:

I am entering data to column B, a 5-digit number. Is there a
formula that I can put in column D that will tell me if the
number is a duplicate.

Supposing your data starts in Row 2, and supposing your
data ends in Row 2000, then in D2 and dragged down could
go:

=COUNTIF($B$2:$B$2000,$B2)

Here's a sample:

B C D
----- --- ---
12345 1
12346 1
12347 1
12348 1
12349 1
12350 1
12351 1
12352 1
12353 2 <------
12353 2 <------
12355 1


You could also, or alternatively, use Conditional Formatting
(Format - Conditional Formatting) to bring dupes to your
attention. The conditional formula could be, e.g.:

=COUNTIF($B$2:$B$2000,$B2)-1

-dman-