Results 1 to 9 of 9

Thread: Identification Formula in Excel

  1. #1

    Identification Formula in Excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    =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
    Regards,
    Zack Barresse

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4

    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

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    No problem. You can add this as a conditional format to highlight the duplicates. What version of Excel are you using?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Hi Ken

    Am using Excel 2002 (10.2614.2625)

    Cheers Reddawn

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    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,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Hey Ken

    Thanks for the help it was much appreciated.

    Everthing working fine now.

    Cheers Reddawn

  9. #9
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •