Conditional Formatting Help.

[tweacle]

Can anyone help.

I have a database with about 6000 entries in it. What im trying to do
is to be able to put new records in and then if any get duplicated on 2
seperate columns it would bring it to my attention.

I have 5 columns of which consist of name, date, station, ref no, cc
no, and amount. These are in columns a-f. Im trying to do it so that
when I put items in columns d&e (ref no and cc no ) if any are
duplicated exactly the same in both columns it highlights it or
something like that.

I had been told to do conditional formatting but this absolutely slows
my computer to a crawl. Ive also been told to but a simlar thing in one
cell and format it to that cell. I come up the true/false but when I
follow the instructions nothing else happens.

To get some sort of idea what I need to do is to it highlight it when
the entries are exactly the same when listed in the 2 colums side by
side.

I.E I dont want it highlighted when I duplicate a entry in say column D
or E seperately but it highlight when column D&E entries are the same as
an earlier entry if that makes sense.

D&E have 1234 5678 What I want is it to highlight it when 1234 5678 are
input alongside each other and not when 1234 is listed in D and 5678 in
E seperately.

Im looking at the possibilty of doing a macro where it verifies the
data and then takes me to the last entry on the file. When the file re
opens it clears the formula so it dosent run slow. Would this Work??


Can anyone help me as to how I can can tackle this in a different way
or if my suggestion would work.

 

[GaryE]

Ok I've got an idea

First name your columns so that you can use them in array formulas.

Add another column called Duplicated to the left of "cc no"

in that column place the following formula
{=if(isblank(e2),"",if(sum(if(ref_no=d2,if(cc_no=e2,1,0)))>1,"y","n"))}

do not put in the curly braces manualy. after entering the formul
press shift-control-enter (this designates the formula as an arra
formula).

This will immediately tell you if you have a duplicate by placeing "Y
in that column if there is a duplicate or "N" if there is not (o
nothing if the data in that row is blank). Now I did not do this wit
6000 records so I don't know if it will slow everything down too much.
Now you apply conditional formating to the cells based on what i
contained in the Duplicate column. (highlight the cells choos
conditional formatting choose formula is type in =if(f1=2,1,0) etc.
but that will certainly slow things down dramatically. If you choos
the conditional formatting approach you could also choose to hide th
duplicate column if you wish.

Hope this helps,
Gary