Checking for Dupes


T

TKnTexas

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
 
Ad

Advertisements

B

Brad

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)
 
G

Guest

This formula should do the trick:

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

HTH,
Elkar
 
D

Dallman Ross

In <1162498243.042396.267400@m73g2000cwd.googlegroups.com>,
TKnTexas said:
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-
 
Ad

Advertisements

T

TKnTexas

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top