Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: VBA Macro to find duplicates from multiple columns

  1. #1
    Join Date
    11th September 2012
    Posts
    9

    VBA Macro to find duplicates from multiple columns

    Dear Ozgrid,

    I have a workbook with multiple columns with differiating data. I would need to compare the values of Column A to every other column with data on it, and highlight duplicates. (Note, only column A should be used as a comparison value. So A should be compared with B C and D, but B should not be compared with C or D.

    Attached example workbook.

    Dummy1.xlsx

    So this is what my start setup looks like.

    User1 User2 User3 User4 User5
    X1 X1 X5 X4 R2
    Y2 Y2 R2 X3 Y2
    Y3 Y3 Y4 Y1 Y9
    X2 Y4 Y7 Y2 X1
    X3 Y5 Y3 Y7
    Y4 X2 X1
    Y5 X2
    X4 X4
    X5
    R2

    And this is what it should look like after running the macro


    User1 User2 User3 User4 User5
    X1 X1 X5 X4 R2
    Y2 Y2 R2 X3 Y2
    Y3 Y3 Y4 Y1 Y9
    X2 Y4 Y7 Y2 X1
    X3 Y5 Y3 Y7
    Y4 X2 X1
    Y5 X2
    X4 X4
    X5
    R2


    The purpose of this macro is to find values from columns B - XX, that aren't found in column A.
    As from this example, the values Y7 and Y9 were found.

    In the data workbook we're using, there's around 70 columns of data.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,486

    Re: VBA Macro to find duplicates from multiple columns

    1) Select B2:E11
    2) Go to Conditional Formatting : Formula: =AND(B2<>"",ISNUMBER(MATCH(B2,$A$2:$A$11,0)))
    3) Choose bold font.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th September 2012
    Posts
    9

    Re: VBA Macro to find duplicates from multiple columns

    Try conditional formatting with this formula for the range User2 to user5

    =SUMPRODUCT(--($A$2:$A$11=B2)*1)=1

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    11th September 2012
    Posts
    9

    Re: VBA Macro to find duplicates from multiple columns

    Dear Jindon & Gopala,

    Thank you for your input, but I was unsuccesful with either of these formulas : (.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th September 2012
    Posts
    9

    Re: VBA Macro to find duplicates from multiple columns

    Hi Tuppari, Have you checked with the ranges as the same solution worked for me

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,486

    Re: VBA Macro to find duplicates from multiple columns

    See
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th September 2012
    Posts
    9

    Re: VBA Macro to find duplicates from multiple columns

    Quote Originally Posted by jindon View Post
    See
    Dear Jindon, your solution now works perfectly, with one but! We have values with * as the last character, and thus excel reads them as wildcards. Is there a workaround for this?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,486

    Re: VBA Macro to find duplicates from multiple columns

    No idea.

    If you upload a sample file, someone else may solve your problem.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    5th October 2012
    Posts
    1

    Re: VBA Macro to find duplicates from multiple columns

    I have a list of 12 groups I have to select common groups in each group. please anyone help me how I will do this

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Macro help find duplicates in two columns
    By jpedges38 in forum EXCEL HELP
    Replies: 3
    Last Post: April 26th, 2012, 06:52
  2. Replies: 11
    Last Post: September 23rd, 2010, 01:01
  3. Duplicates in multiple columns
    By mahoulihan1 in forum EXCEL HELP
    Replies: 1
    Last Post: September 15th, 2010, 18:47
  4. Replies: 1
    Last Post: August 17th, 2010, 07:58
  5. Remove Duplicates Of Multiple Columns
    By Tony Mos in forum EXCEL HELP
    Replies: 43
    Last Post: August 7th, 2007, 14:00

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
  •  
porno