Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

VBA Macro to find duplicates from multiple columns

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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.

    Comment


    • #3
      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

      Comment


      • #4
        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 : (.

        Comment


        • #5
          Re: VBA Macro to find duplicates from multiple columns

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

          Comment


          • #6
            Re: VBA Macro to find duplicates from multiple columns

            See
            Attached Files

            Comment


            • #7
              Re: VBA Macro to find duplicates from multiple columns

              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?

              Comment


              • #8
                Re: VBA Macro to find duplicates from multiple columns

                No idea.

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

                Comment


                • #9
                  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

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X