+ Reply to Thread
Results 1 to 3 of 3

I need to club two column values and search with it to find duplicate values

  1. #1
    Registered User
    Join Date
    09-12-2018
    Location
    Mumbai, india
    MS-Off Ver
    2016
    Posts
    0

    I need to club two column values and search with it to find duplicate values

    Concatenate values from two different column and use that as range in match function without saving values in any other column

    I need to highlight values which are duplicate in two column values.

    Untitled picture.png

    I have below code which is working only for B column but not combining C column.


    Public Sub Validate1()
    CheckMaterialNumber "Sheet1", "B", "C", 3
    End Sub

    Public Sub CheckMaterialNumber(DestinationSheet As String, MaterialNumCol As
    String, PlantCol As String, startrow As Long)
    Dim EndRow As Long
    Dim row As Long
    EndRow = Worksheets(DestinationSheet).Range("B" & Rows.Count).End(xlUp).row


    For row = startrow To EndRow

    Dim EMaterilNumber As String
    EMaterilNumber = Worksheets(DestinationSheet).Range(MaterialNumCol & CStr(row)).Value


    Dim Tmatch As Long
    On Error Resume Next
    Tmatch = application.WorksheetFunction.CountIf(Worksheets(DestinationSheet).Range("B2:B" & EndRow), EMaterilNumber)

    If Tmatch > 1 Then
    Worksheets(DestinationSheet).Range(MaterialNumCol + CStr(row)).Interior.ColorIndex = 3
    Else
    Worksheets(DestinationSheet).Range(MaterialNumCol + CStr(row)).Interior.ColorIndex = 43
    End If


    Next row
    End Sub

  2. #2
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,142
    Hello Abhimanyu.bade,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2010, 2016
    Posts
    6,156

    Re: I need to club two column values and search with it to find duplicate values

    Hi,

    As you may be read description of this sub-forum - this one is solely to introduce yourself.

    So here I'd say: welcome to the forum. And that's it.

    With your merit question, please go to appropriate sub-forum. Look what sub-forums we have: http://www.excelforum.com/microsoft-...el-help-forum/ and post a new thread in one of them (the most appropriate one).
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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