Help With A College Project

  • Thread starter Thread starter Dr_Phil
  • Start date Start date
D

Dr_Phil

hi,

How would you, using an if statement cheack to see if an entry in
list has been replicated?

eg.

a
b
c
d
e
f
g
a
h

"a" has been replecated-how wod u check for that?.....

i tried

=if(A1 = A2:A9,"replication","no replication")

and i repeted this for each line that i wanted but it didn
work.....any one know how to do it
 
You could insert this into B1 and copy down:

=IF(COUNTIF($A$1:A1,A1)>1,"Duplicate","")

HTH
Jason
Atlanta, GA
 
Dr_Phil,

In B1, enter

=IF(COUNTIF($A$1:$A$20=A1)>1,"replication","no replication")

and copy down

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
thanx for those.....how would i then delete the duplicate cell.....

i changed the code to the following:

IF(COUNTIF($A$1:A1,A1)>1,A1="","Not Duplicate")

but i just get "false" in the cell
 
Right click on it and left click on delete.

you will get "false" when A1 is not equal to "". You should probably remove
the A1= from that formula and just leave it with "".
 
Is there no way to do it within the cell as i have to document th
methods i have used and i am not to familiar with vba
 
Change it to

B1: =IF(COUNTIF($A$1:A1,A1)>1,"",Not Duplicate")

Then do an autofilter on column B, select blanks, and then delete them.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You can't delete a cell with your formula (or any formula). A1="" is
evaluated by Excel as either true or false.
 
Ok Ok......U have all been gr8 but i think ur all not sure of the ai
(in a nice way)

lets say we have:

0
1
2
4
3
5
0
8
3

The code:

=IF(COUNTIF($A$1:$A$20=A1)>1,"replication","no replication")

Will tell me where there is a replication, this will be next to the 3'
and the 0's.

BUT

instead of the code saying replication i want it to delete th
replicated data.

Sorry if i havnt been clear.

thank
 
Hi
I provided a link to a macro for you. You'll need VBA for this as a
formula cannot delete cell values
 
Here's what you can do without VBA:

1) Insert this into row 1 and copy down:
=IF(COUNTIF($A$1:A1,A1)>1,"Duplicate",1)
2) Select the range with the formulas (should already be
selected).
3) Press F5 > Special
4) Click "Formula" and uncheck everything below it
except "text".
5. Press <Ctrl><->.
6. Select "Entire row" and press OK.

Jason
 
Thanx A Ton Jason.

But where do i put the macro....how do i enter it? and can u posibl
explain it for me? line by line...if its not too much hassle?

than
 
but Jason 's way doesn't use a macro!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
and I provided you a non-VBA solution as did Jason. We all understand what
you want, but it seems that you do not understand enough of Excel to
interpret the solutions offered.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top