+ Reply to Thread
Results 1 to 3 of 3

vba code to find Duplicate Values and Newest date(Max) date in the Corresponding Column(ce

Hybrid View

  1. #1
    Registered User
    Join Date
    09-08-2013
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    2

    vba code to find Duplicate Values and Newest date(Max) date in the Corresponding Column(ce

    Friends i'm Creating Macro for "Locating Duplicate values in a Column n new date(Max date in Corresponding column)....i'm not Getting Logic for that pls Help me in the Coding...

    i Data is like this

    x y Result
    3 8/6/2013 8/6/2013
    3 8/5/2013
    3 8/4/2013
    2 9/3/2013 9/2/2013
    2 9/2/2013
    2 9/1/2013
    1 9/6/2013 9/6/2013
    1 9/3/2013
    1 9/2/2013
    i tried this by sorting..its Working Better for Small Data..but not for Large... Even i Want to Reduce the code Length..
    Here is My code...

        
    
    Sub Macro2()
    
    Range("a1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "fstcol"
    Range("b1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "seccol"
    Range("a1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
     Selection.Name = "myrange"
     Range("a1").Select
     Range("myrange").Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("fstcol") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("seccol") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("myrange")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("a1").End(xlToRight).Offset(0, 1).Value = "Flag"
        Range("a1").End(xlToRight).Offset(1, 0).Select
        ActiveCell.Value = 1
        Do While ActiveCell.Offset(0, -2).Value <> "" And ActiveCell.Offset(1, -2).Value <> ""
         ActiveCell.Offset(1, 0).Select
    If ActiveCell.Offset(0, -2).Value = ActiveCell.Offset(1, -2).Value Then
               ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
        End If
        Loop
        Range("a1").End(xlToRight).Offset(1, 0).Select
        Do While ActiveCell.Offset(0, -2).Value <> ""
        If ActiveCell.Value = 1 Then
        ActiveCell.Value = ActiveCell.Offset(0, -1).Value
        Else: ActiveCell.Value = ""
        End If
        ActiveCell.Offset(1, 0).Select
        Loop
        ActiveCell.EntireColumn.Select
        Selection.NumberFormat = "m/d/yyyy"
        Range("a1").Select
    End Sub
    Last edited by Pradu; 09-10-2013 at 05:25 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: vba code to find Duplicate Values and Newest date(Max) date in the Corresponding Colum

    no need for such complicated macro no need for sorting etc
    get uniuq values in x below the data and loop through the uniuq x values and use mim if formula

    see below my macro

    Sub test()
        Dim x As Range, filt As Range, cfilt As Range, myformula As String
    
        Range(Range("A1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete
    
        Set filt = Range("A1").End(xlDown).Offset(5, 0)
        Set x = Range(Range("A1"), Range("A1").End(xlDown))
        x.AdvancedFilter xlFilterCopy, , filt, True
        Set filt = filt.Offset(1, 0).Resize(filt.CurrentRegion.Rows.Count - 1)
        'MsgBox filt.Address
        For Each cfilt In filt
            myformula = "=MAX(IF(" & x.Address & "=" & cfilt & "," & x.Offset(0, 1).Address & "))"
            cfilt.Offset(0, 1) = Application.Evaluate(myformula)
            cfilt.Offset(0, 1).NumberFormat = "m/d/yyyy"
        Next cfilt
    End Sub
    I am not an expert. better solutions may be available
    $$$$venkat1926$$$$@gmail.com

  3. #3
    Registered User
    Join Date
    09-08-2013
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: vba code to find Duplicate Values and Newest date(Max) date in the Corresponding Colum

    Hi Venkat,
    Thanks for the Solutions, its Working Fine for My data and thanks for Ur Code n Valuable time...

+ 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. Hi Guys
    By xllbenllx in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-16-2013, 08:00 PM
  2. Hi Guys
    By blueorange in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 01-04-2013, 12:04 PM
  3. Hi Guys
    By blueorange in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-28-2012, 12:51 AM
  4. Hi Guys
    By ray_78 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-18-2012, 07:58 PM
  5. Hi Guys,
    By jaganr10 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-18-2012, 07:45 PM

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