+ Reply to Thread
Results 1 to 7 of 7

DUPLICATION FOR COMBINATION OF 3 DISTINCT COLUMNS vba coding

  1. #1
    Registered User
    Join Date
    04-02-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    6

    DUPLICATION FOR COMBINATION OF 3 DISTINCT COLUMNS vba coding

    im using vba coding for my excel sheet application.

    where the user enters all the data in the excel sheet and by clicking on validate button a text file will be generated ,and if the user enters any wrong data say text in where numeric fields are only allowed it shows an alert and text file wnt be generated.

    now i require some help if the user enters duplicate values ie combination of columns A,D,E are unique if any duplicate row is found withsame values for A,D,E
    alert should be shown ie

    col A col B colC col D col E col F
    1 2 3 4 5 6
    1 3 4 4 5 7
    1 2 3 4 6 6

    the output should be



    col A col B colC col D col E col F
    1 2 3 4 5 6

    1 2 3 4 6 6

    my code is:

    Public Function DeleteDuplicaterecords(Opt As String) As Boolean
    Dim x As Long
    Dim LastRow As Long

    If Opt = "P" Then
    sheet1.Select
    Else
    sheet2.Select
    End If

    LastRow = Range("A65536").End(xlUp).Row
    For x = LastRow To 1 Step -1



    If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 And Application.WorksheetFunction.CountIf(Range("D1:D" & x), Range("D" & x).Text) > 1 And Application.WorksheetFunction.CountIf(Range("E1:E" & x), Range("E" & x).Text) > 1 Then


    MsgBox "Duplicate Record is found,combination for Tin no,Invoice No and Invoice Date are Unique"

    Range("A" & x).Select

    DeleteDuplicaterecords = False
    Exit Function
    End If


    Next x
    DeleteDuplicaterecords = True
    End Function

  2. #2
    Forum Contributor johnjohns's Avatar
    Join Date
    11-19-2007
    Location
    Dubai, UAE
    MS-Off Ver
    2003 and 2007
    Posts
    526

    Re: DUPLICATION FOR COMBINATION OF 3 DISTINCT COLUMNS vba coding

    Please take a few moments to read the forum rules and edit your post with adding code tags.
    regards

    johnjohns

    When you are not sure where to go, every road takes you there!

  3. #3
    Registered User
    Join Date
    04-02-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: DUPLICATION FOR COMBINATION OF 3 DISTINCT COLUMNS vba coding

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-02-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: DUPLICATION FOR COMBINATION OF 3 DISTINCT COLUMNS vba coding

    can u help me with this problem im unable to solve it

  5. #5
    Forum Contributor johnjohns's Avatar
    Join Date
    11-19-2007
    Location
    Dubai, UAE
    MS-Off Ver
    2003 and 2007
    Posts
    526

    Re: DUPLICATION FOR COMBINATION OF 3 DISTINCT COLUMNS vba coding

    If you look for duplicates with combination of A,D,E columns always, try the below after selecting the data (Excel 2007+ versions)

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-02-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: DUPLICATION FOR COMBINATION OF 3 DISTINCT COLUMNS vba coding

    where to place that code in my coding im getting error

  7. #7
    Registered User
    Join Date
    04-02-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: DUPLICATION FOR COMBINATION OF 3 DISTINCT COLUMNS vba coding

    im getting an error for that code can u just read my code and tell where to put it

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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