+ Reply to Thread
Results 1 to 20 of 20

highlight duplicate reference numbers in column A

  1. #1
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    highlight duplicate reference numbers in column A

    hi all,

    I managed to find this great bit of code some time ago and would now like to add to it. The code basically identifies any duplicates in column A and then lists the results in a message box,
    e.g.:
    Duplicate Name: REF12345
    Rows: 2,3

    Works great for 1 or 2 duplicates but if there are many it becomes quite difficult to keep track. I'd like keep the code as is but add highlighting to the identified cells.

    Please Login or Register  to view this content.
    Last edited by Gti182; 06-06-2014 at 03:50 AM.

  2. #2
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: highlight duplicate reference numbers in column A

    This will hilite the duplicate values in column A

    Please Login or Register  to view this content.
    Click on the star if you think I helped you

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: highlight duplicate reference numbers in column A

    Hi

    why don't you just use a Conditinal Formatting rule in column A to highlight the duplicates? Something like

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    How can you use Conditional Formating.

    --In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: highlight duplicate reference numbers in column A

    You don't need any formulas or macros if you use Excel 2007, 2010 or 2013. Simply go to Conditional Formatting, Highlight Cells rules and choose Duplicate values.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: highlight duplicate reference numbers in column A

    thanks for the suggestions so far.

    I've considered the conditional formatting route but preferably like to go the highlighting route to save the user from having to remove the conditional formatting rules

  6. #6
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: highlight duplicate reference numbers in column A

    bump to first page

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: highlight duplicate reference numbers in column A

    Perhaps

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: highlight duplicate reference numbers in column A

    Fotis1991 i get a Run-Time error '5': Invalid procedure call or argument on this bit of code

    Please Login or Register  to view this content.

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: highlight duplicate reference numbers in column A

    Why? I don't get it!!
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: highlight duplicate reference numbers in column A

    lol i don't get it either, get the same error in your file. I'm using Excel 2010 if that makes any difference?

  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: highlight duplicate reference numbers in column A

    I work in Excel 2010 too and i have no problem at all using this code in my example sheet. Sorry. No idea for why is not working for you.

  12. #12
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: highlight duplicate reference numbers in column A

    hmm... let me have a play around with it, anybody else having the same error?

  13. #13
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: highlight duplicate reference numbers in column A

    Gti, question: did you try to code I provided? It highlights the duplicate values throughout the whole column.

  14. #14
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: highlight duplicate reference numbers in column A

    Quote Originally Posted by adyteo View Post
    Gti, question: did you try to code I provided? It highlights the duplicate values throughout the whole column.
    Hi adytero yes i did, it seemed to work but it highlighted column B rather than column A?

  15. #15
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: highlight duplicate reference numbers in column A

    this?
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: highlight duplicate reference numbers in column A

    kalak that works beautifully, even less code than my original which i thought was pretty efficient

    Is there a way to remove the absolute "$" references from the message box at all? e.g. from $A$2,$A$3 to A2,A3 or Row2, Row3?

  17. #17
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: highlight duplicate reference numbers in column A

    Quote Originally Posted by Gti182 View Post
    Is there a way to remove the absolute "$" references from the message box at all? e.g. from $A$2,$A$3 to A2,A3 or Row2, Row3?
    Change the line near the bottom to (changed bit in red)
    Please Login or Register  to view this content.
    Rows instead of cell addresses requires a bit more modification, but can be done if you like.

  18. #18
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: highlight duplicate reference numbers in column A

    that my friend is simply awesome! Thanks so much, much appreciated!

  19. #19
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: highlight duplicate reference numbers in column A

    You're very welcome.

  20. #20
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    455

    Re: highlight duplicate reference numbers in column A

    the more i look at that code the more i become inspired

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Highlight duplicate numbers with different colors in two columns
    By 17_types in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-28-2013, 04:05 AM
  2. [SOLVED] Highlight duplicate numbers in rows and coloumns from a different range.
    By dazd1970 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-21-2013, 09:23 AM
  3. Conditional Formatting to highlight duplicate numbers in a range
    By mungel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-21-2013, 02:54 PM
  4. Highlight duplicate numbers
    By nakata75 in forum Excel General
    Replies: 2
    Last Post: 09-22-2012, 12:31 PM
  5. highlight duplicate numbers
    By css in forum Excel General
    Replies: 8
    Last Post: 09-16-2005, 05:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1