Results 1 to 14 of 14

Excel Macro to find unique distinct value based on Criteria

Threaded View

  1. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,523

    Re: Excel Macro to find unique distinct value based on Criteria

    See the attached,
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rws As Long, Rng As Range, c As Range, s As String
        Dim rw1 As Long, r2 As Range, j19 As Range
        Dim rw2 As Long, r3 As Range, L19 As Range, c1 As Range, rng2 As Range
        Rws = Cells(Rows.Count, "A").End(xlUp).Row
        Set Rng = Range(Cells(2, 1), Cells(Rws, 1))
        Set rng2 = Range(Cells(2, 2), Cells(Rws, 2))
    
    
        Set j19 = Range("J19")
        Set L19 = Range("L19")
        If Target.Count > 1 Then Exit Sub
        If Target.Address = "$H$19" Then
            s = Target.Value
    
            Range("P4:P1000").ClearContents
            Range("R4:R1000").ClearContents
    
            '--------Country-------
    
            For Each c In Rng.Cells
                If c = s Then
                    y = Application.WorksheetFunction.CountIf(Range(Cells(1, 2), Cells(c.Row, 2)), c.Offset(, 1))
                    If y = 1 Then Cells(Rows.Count, "P").End(xlUp).Offset(1, 0) = c.Offset(0, 1)
                End If
            Next c
            rw1 = Cells(Rows.Count, "P").End(xlUp).Row
            Set r2 = Range(Cells(4, "P"), Cells(rw1, "P"))
            r2.Name = "cty"
            With j19.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                     xlBetween, Formula1:="=cty"
            End With
        End If
        '--------Centers-------
        If Target.Address = "$J$19" Then
            s = Target.Value
    
            For Each c1 In rng2.Cells
                If c1 = s Then
                    x = Application.WorksheetFunction.CountIf(Range(Cells(1, 3), Cells(c1.Row, 3)), c1.Offset(, 1))
                    If x = 1 Then Cells(Rows.Count, "R").End(xlUp).Offset(1, 0) = c1.Offset(0, 1)
                End If
            Next c1
            rw2 = Cells(Rows.Count, "R").End(xlUp).Row
            Set r3 = Range(Cells(4, "R"), Cells(rw2, "R"))
            r3.Name = "ctr"
            With L19.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                     xlBetween, Formula1:="=ctr"
            End With
        End If
    
    
    End Sub
    Attached Files Attached Files
    Last edited by davesexcel; 12-20-2014 at 09:11 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create a distinct list based on other criteria
    By tomtheappraiser in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-29-2014, 12:59 PM
  2. Replies: 5
    Last Post: 03-13-2012, 06:05 AM
  3. Pivot Tables-To Find Count of Unique(Distinct)User id within the same Journal Entry.
    By Deepthik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2010, 06:09 AM

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