Identification Formula in Excel

R

reddawn

New member
Joined
Jun 26, 2011
Messages
10
Reaction score
0
Points
0
Hi all

Is someone able to help me with a formula for excel that would allow excel to identify if data has been input twice. For example if I lifted information from one document to put into an excel document and it is mixed alpha and numerical (awdhq58749648), what formula can I use in excel to identify if the same (awdhq58749648) has been input into the worksheet somewhere else.

Cheers Reddawn :confused2:
 
=IDENTIFYTHEDUPLICATEHEREPLEASE()

Okay, I'm just kidding, but welcome to the board!

You can use a standard COUNTIF() function for that. Pass your range to look in first, then the criteria you would like to look in that range for.

HTH
 
Hi RedDawn,

Like Zack says, COUNTIF() will do what you want. If you have your data starting in cell A5, you could run this down another column to check: =COUNTIF($A:$A,A5) Anywhere that shows a number greater than 1 is a duplicate.
 
Identification Formula

Hi Zack and Ken
Thanks for the info and whats needed for identifying duplication, it is much appreciated. Can you guys help me with one more bit of info. Once the formula identifies a duplicate, what can I add to the formula to highlight the duplicate/s.
Yeah, and I wish it were as easy as =IDENTIFYTHEDUPLICATEHEREPLEASE()
Zack :) Oh and remember guys I am a novice to excel, really only understanding the basic functions like add subtract etc.

Cheers Reddawn
 
No problem. You can add this as a conditional format to highlight the duplicates. What version of Excel are you using?
 
Hi Ken

Am using Excel 2002 (10.2614.2625)

Cheers Reddawn
 
Okay, so assuming that the data you want to evaluate is from A5:A100, you would do the following:
-Select A5:A100 (Starting with A5)
-Go to Format-->Conditional Formatting
-Change the first box to "Formula is"
-Paste in the following: =COUNTIF($A:$A,A5)
-Click the Format button --> Patterns
-Select the colour you'd like to highlight duplicates with (Yellow is probably a good one)
-Click OK --> OK

At that point, any duplicates in column A should highlight automagically for you.

Hope it helps,
 
Hey Ken

Thanks for the help it was much appreciated.

Everthing working fine now.

Cheers Reddawn
 
Hi

Select the complete range of cells where you want the formatting to occur
Format>Conditional Formatting>choose dropdown to select "Formula is">enter your formula e.g.
=COUNTIF($A:$A,A5)
Select Format and choose how you want to colour the cells then press OK
 
You must log in or register to reply here.
Back
Top