Match function?

[Emma]

Hi,
I have hunders of numbers in columns and I want to set a
conditional format that will highlight a cell when I type
in a number if it already exists.

Example:
A B C D
11 15 40
12 16 47
20 33 60

If I were to type the number 33 in column D I would like
to the cell to be highlighted.

I know that I can use the following formula but that only
lets me look at one column, I want to look at the all:
=MATCH(D1,A1:A35,0)

Can anyone help me with this please.
Thanks heaps.
Em

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

 

[Aladin Akyurek]

Select A1:C35.
Activate Format|Conditional Formatting.
Choose Formula Is for Condition 1.
Enter the following in the white box:

=A1=$D$1

Activate the Format button.
Choose the desired format from the appropriate tab.
Click OK, OK.

 

[Bob Phillips]

Emma,

Try this formula in the conditional format

=COUNTIF($A$1:$D$100,A1)>1

 

[Guest]

Thanks guys I got it working, cool

-----Original Message-----
Hi
see:
http://www.cpearson.com/excel/duplicat.htm#HighlightingDup licates

--
Regards
Frank Kabel
Frankfurt, Germany


.

 

[Guest]

Okay now I have that sorted can you tell me if it is
possible to make it tell you which cell it is a duplicate
of? I have 600 duplicate numbers (roughly) and I need to
know exactly which cell goes with which. I am more
concerned with it happening when a new number is typed not
with the existing list and even if it sends it to another
worksheet it would really help.

Would you know if that was possible too?
And thanks in advance your help is very much appreciated

 

[Ken Wright]

Select the range you intend to populate, eg assuming A1100:-

Do Format / Conditional Formatting, then change 'cell value is' to 'formula is'
and put in the following formula

=AND(A1<>"",COUNTIF($A$1:$P$100,A1)>1)

Choose your format, eg red pattern and then hit OK. Now whenever you type in a
number that has already been used it will highlight both to show you.

Note, the above assumes you selected A1100, hence the A1 in the formula. If
you selecet any other range you need to substitute the $A$1:$P$100 for that
range and then switch the A1 for the top left cell of that range.

 

[...Patrick]

if i understand ...

in ALL cells with your data , chose conditionnal formatting with this
formula :

=countif($A$1:$C$1,A1) > 1


--
....Patrick
Quoi que vous fassiez, faites le bien .
Mail: http://cerbermail.com/?KPW0tTCjFw
Connectez vous sur ce forum par :
news://msnews.microsoft.com/microsoft.public.fr.excel