Identification Formula in Excel

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:
 

Zack Barresse

Super Moderator
Staff member
Joined
Mar 16, 2011
Messages
112
Reaction score
0
Points
0
Location
Oregon, United States
Excel Version(s)
365, Online, iOS, iOS for iPad
=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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 

reddawn

New member
Joined
Jun 26, 2011
Messages
10
Reaction score
0
Points
0
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
No problem. You can add this as a conditional format to highlight the duplicates. What version of Excel are you using?
 

reddawn

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

Am using Excel 2002 (10.2614.2625)

Cheers Reddawn
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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,
 

reddawn

New member
Joined
Jun 26, 2011
Messages
10
Reaction score
0
Points
0
Hey Ken

Thanks for the help it was much appreciated.

Everthing working fine now.

Cheers Reddawn
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
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
 
Top