Excel formula to find identical cells

[Raymond RUSSELL]

Hello all

Help please for a relative beginner when it comes Excel !

I have a very long terminology list in an Excel worksheet -
column F as source language
column G as target language
sorted with a header line
primarily on F
secondarily on G.

What I want to do is use a formula
that would insert the word DUPLICATE in column H
wherever two cells one below the other in column F are identical.

Where should I put this formula ?
What should the formula be ?
How should I enter it ?
How should I run it ?

Thanks very muchg in advance
from Ray

 

[Frank Kabel]

Hi Ray
maybe the following is what you want:
Enter the following formula in H1
=IF(F1=F2,"Duplicate","")
copy this formula down

 

[Raymond RUSSELL]

Hello Frank

Can I enter this formula in H1
even if I have a header row ?

What do you mean by "copy down" ?

Thanks again from Ray

 

[Frank Kabel]

Hi ray
if you have a heading row enter the following in H2 (if this is your
first row)
=IF(F2=F3,"Duplicate","")

With copy down I meant the following:
- copy this cell (e.g. with CTRL+C)
- select all rows for which this formula should be applied
- Enter CTRL+V to insert the formula (the cell references will change
automatically)

 

[Raymond RUSSELL]

Hello again again Frank

Yes that works ! Thanks !

Three more questions :

1) Is there any way I can speed up / automate the process of "copying down"
?
(The worksheet has about 50000 rows.)

2) Is there any way I can stop the word "FALSE"
being written in all cells where not identical

3) Can I make the word "DUPLICATE" in another font / color ?

Best regards and thanks again
from Ray

 

[Rusty_nl]

Regarding your questions (I hope Frank doesnt mind me asnwering some)

1. What you can do is write a macro to copy down. Or the quickest wa
that I know is put a value at the bottom row in the column that you ar
copying in. That copy the formula and press "End" and then "Arrow down
button. This will take you to the first cell that has a value, bein
the last cell that you need to copy to.

I hope I wrote that down clear enough.

2. If you are getting FALSE in the sheets that are not duplicate i
looks like your formula is not complete. Make sure you add the last "
in the formula.

You might have: IF(F3=F4;"Duplicate")
Where IF(F3=F4;"Duplicate";"") should not give any FALSE's

3. You can use conditional formatting to make the word Duplicate stan
out. Select the column that you use for the Duplicate. Go to Format -
Conditional Formatting.

Condition 1:
Cell Value Is: Equal To : Duplicate

Press the Format button and select whatever font and or color and o
fill that you like.

 

[Frank Kabel]

Hi
don't midn at all
just one addition

Regarding your questions (I hope Frank doesnt mind me asnwering some)

1. What you can do is write a macro to copy down. Or the quickest way
that I know is put a value at the bottom row in the column that you
are copying in. That copy the formula and press "End" and then "Arrow
down" button. This will take you to the first cell that has a value,
being the last cell that you need to copy to.

or double click on the lower right of the selected cell. this will fill
the formula for all rows