Check for duplicate entries???

[FISH]

Sorry in advance for the newbie type question

I'm going to be making a long list of items and prices. I will be getting to
the point I will come across the same item again and want to make sure I
don't have duplicate entries.

Is there a way in Excel to check and see if that entry has already been
entered in the workbook?

I assume there are a couple ways, but how would you set things up to check
to prevent duplicate entries?

Thanks

(I'm using Excel 2002)

 

[Dave Peterson]

You may want to look at Chip Pearson's site. He has lots of techniques to work
with duplicates:
http://www.cpearson.com/excel/Duplicates.aspx

 

[Shane Devenshire]

Hi,

1. In Excel 2007 you can turn on a feature for highlighting duplicates
2. In all versions you can create formulas or conditional formatting or
data validation for checking for duplicates.
a: Formula - =COUNTIF(A$1:A$1000,A1) copy down, assuming your duplicates
would be in column A. Anytime a duplicate appears this formula will return a
number larger than 1
b: Conditional Formatting - highlight the A1:A1000 range and put the above
formula into the Format, Conditional Formatting, Formula is box and pick a
Format.
c: Data Validation: You can prevent duplicate entries in a range as follows:

1. Highlight the range, lets say A1:A100
2. Choose the command Data, Validation
3. Under Allow choose Custom
4. Enter the following formula in the Formulas box:
=COUNTIF(A$1:A$1000,A1)=1

One thing to keep in mind - if the user copies and pastes data into the
range where the Data Validation is, it is wiped out and anything can be
entered.

If this is helpful, please click the Yes button.

Cheers,

 

[FISH]

Thank you. I went to his site and tried the "Highlighting Duplicate Entries"
instructions, but it doesn't work properly.


For example: If I have an item in C6 and I enter that same item in C50, only
C5 (one line above) gets highlighted. The duplicate I entered in C50 and
other duplicate in C6 remain unchanged.


I followed the instructions on that site and have checked the code a few
times. My exact code (because my range is from C5:C3000) is:

In Conditional Formatting
I change the first box to "Formula is"
Then I enter this code in the next box: =COUNTIF($C$5:$C$3000,C5)>1



Any idea what I'm doing wrong?


I would like to set this up so when I enter in a duplicate item, both
duplicate items are highlighted.



Thanks!!!





Here are the instructions from the site that I followed:

This first example will highlight duplicate rows in the range B2:B11. Select
the cells that you wish to test and format, B2:B11 in this example. Then,
open the Conditional Formatting dialog from the Format menu, change Cell
Value Is to Formula Is, enter the formula below, and choose a font or
background format to apply to cells that are duplicates.

=COUNTIF($B$2:$B$11,B2)>1

The formula above, when used in Conditional Formatting, will highlight all
duplicates. That is, if the value 'abc' occurs twice in the list, both
instances of 'abc' will be highlighted. This is shown in the image to the
left, in which all occurrences of 'a' and 'g' are higlighted.

 

[Dave Peterson]

I think you messed up when you applied the conditional formatting.

The cells with the values should be highlighted--not the cells above the
duplicates.

Make sure you select C5:C3000
and with C5 the activecell, do the format|conditional formatting
formula is:
=countif($c$5:$c$3000,c5)>1

====
Just a personal choice.

I like to use a helper column with the same formula. Then I can use
data|filter|autofilter to see the True's (duplicates).

I find it easier than scrolling looking for color changes.

 

[FISH]

I'm still doing something wrong. Obviously I'm a newbie so just want to make
sure I'm reading the instructions correctly.

When you say make sure the cells within the values are highlighted, do you
simple mean start at the beginning of the range and drag the highlighted
section all the way to the end of the range? So just start at C5 and hold
the left mouse button down while scrolling all the way down to C3000, right?

Once that range is highlighted, how do I make sure C5 is the active cell
(without the highlighted range from C5-C300 to stop being highlighted)?


Thanks again and sorry for the very basic help I'm needing help with.

 

[Dave Peterson]

That's exactly what I mean.

You can tell the difference in the activecell by its color. It's
different--usually white while the rest of the selection is blue.

I'm still doing something wrong. Obviously I'm a newbie so just want to make
sure I'm reading the instructions correctly.

When you say make sure the cells within the values are highlighted, do you
simple mean start at the beginning of the range and drag the highlighted
section all the way to the end of the range? So just start at C5 and hold
the left mouse button down while scrolling all the way down to C3000, right?

Once that range is highlighted, how do I make sure C5 is the active cell
(without the highlighted range from C5-C300 to stop being highlighted)?

Thanks again and sorry for the very basic help I'm needing help with.

 

[FISH]

Thought I was doing that before, but it worked this time.

THANKS for all the help !!!

 

[FISH]

I got it "kinda" working...lol. I sent the format to shade the background of
the cell red. When I enter in a duplicate item, the original item does get
highlighted, buy the one I just entered does not. This doesn't work to well
as usually the original item will be so far from the newly entered one I
won't see it until later when I happen to view that section of the workbook.

Any idea why it is not changing the new entered cell?

Thanks again.

 

[Dave Peterson]

Share the formula you used.
Share the cells you applied the formula to.
Share the addresses for the original item and the second item.
Share the values that you entered into the two cells.

If you try it on a brand new worksheet in a brand new workbook (and a smaller
range), can you get it to work?

I got it "kinda" working...lol. I sent the format to shade the background of
the cell red. When I enter in a duplicate item, the original item does get
highlighted, buy the one I just entered does not. This doesn't work to well
as usually the original item will be so far from the newly entered one I
won't see it until later when I happen to view that section of the workbook.

Any idea why it is not changing the new entered cell?

Thanks again.

 

[FISH]

Got it working with one exception. When I sort the entries then it no longer
works.

It was working fine until I sorted the entries by alphabetic order. Now it
doesn't work anymore. Could it be just from sorting or maybe I must have
done something else also.

 

[Dave Peterson]

If you used format|conditional formatting, I'd reselect the range and reapply
the conditional formatting.

Or maybe you could use the entire column in your formula.

Got it working with one exception. When I sort the entries then it no longer
works.

It was working fine until I sorted the entries by alphabetic order. Now it
doesn't work anymore. Could it be just from sorting or maybe I must have
done something else also.

 

[FISH]

Yes, I could use the entire column in my formula. What would I change to
make it work that way?

 

[Gord Dibben]

=COUNTIF($C:$C,$C1)>1

Rather than use CF to highlight duplicates you may want to use Data
Validation to prevent adding a duplicate.

Also from Chip's site.................

=COUNTIF($C:$C,$C1)=1

Entered in Data>Validation>Allow>Custom

Column C would be pre-selected before applying the DV


Gord Dibben MS Excel MVP

Yes, I could use the entire column in my formula. What would I change to
make it work that way?