Results 1 to 6 of 6

Thread: Extract Unique Values Based On Dependant Combobox Selections

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    Extract Unique Values Based On Dependant Combobox Selections

    hi all
    i have a source sheet and a resultant sheet. i have a form with comboboxes on which gets it data from the source sheet.
    i have 3 dependant combobox on the form. combobox 1 is populated on userform_initialize, combobox 2 is then populated by a unique list of values based on combobox 1, combobox 3 is populated by a unique list of values based on combobox 2.
    i now need to load the unique list of resultant values from combobox 3 in a dynamic range or array and print it to a sheet using a command button. here are two conditions though,
    1. the columns in the resultant sheet is: A = District, B = Town, C = Name and D = Number. each of the comboboxes represents a column. so i need to print the unique numbers in column D to the resultant sheet, and
    2. it has to actually duplicate the unique values under each of the other columns.

    so in the resultant sheet, once the process is completed and it is printing say 4 unique numbers under column D, it has to duplicate from column A (District) to column C(Name) those values that belongs to column D(number).
    the result would look like this after clicking the command button:
    DISTRICT TOWN NAME NUMBER
    NORTH A JOHN A123
    NORTH A JOHN A786
    NORTH A JOHN B124
    NORTH A JOHN B456

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=320960#p320960
    https://eileenslounge.com/viewtopic.php?p=320957#p3209573
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-04-2024 at 10:24 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Can be done. But to make it easier for the developer, it would be better to have a sample working model. Can you share your userform and vba code?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    extract unique values based on dependant combobox selections

    attached please find a sample workbook. as you will see, i have used a listbox on the form to show the unique numbers.

    regards

    Quote Originally Posted by Excel Fox View Post
    Can be done. But to make it easier for the developer, it would be better to have a sample working model. Can you share your userform and vba code?
    Attached Files Attached Files

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Just made the necessary correction to your specific need, and didn't really look at the formatting of the code. Use this for the submit button

    Code:
    Private Sub BtnSubmit_Click()
    Dim wsS As Worksheet
    Dim wsR As Worksheet
    
    Application.ScreenUpdating = False
    
    Dim irow As Long, lngLoop As Long
    
    Set wsS = Worksheets("Resultant")
    'find first row in database
    irow = wsS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With wsS
    For lngLoop = 0 To Me.ListStaIncNo.ListCount - 1
    .Range("A" & irow + lngLoop).Value = Trim(Me.BxStaDistrict.Value)
    .Range("B" & irow + lngLoop).Value = Trim(Me.BxStaTown.Value)
    .Range("C" & irow + lngLoop).Value = Trim(Me.BxStaName.Value)
    .Range("D" & irow + lngLoop).Value = Me.ListStaIncNo.List(lngLoop)
    .Range("E" & irow + lngLoop).Value = Date
    .Range("F" & irow + lngLoop).Value = Time
    .Range("G" & irow + lngLoop).Value = Environ$("USERNAME")
    Next lngLoop
    End With
    
    MsgBox "Data succesfully saved to database"
    
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
    End Sub
    In addition, there's a fault with your listbox getting refreshed. The items has to be cleared first, before loading again with other criterias via the combobox. For that, I've added one line in this routine

    Code:
    Private Sub BxStaName_AfterUpdate()
    
        Dim Cl     As Range
        Dim ClAddress As String
        With Me
        With Sheets("Source")
            Set rSource = .Range(.Cells(1, 4), .Cells(.Rows.Count, 1).End(xlUp))
        End With
        'if no selection in OIC quit
            If .BxStaName.ListIndex < 0 Then Exit Sub
            Set Cl = rSource.Find(Me.BxStaName.Value, LookIn:=xlValues)
            If Not Cl Is Nothing Then
                ClAddress = Cl.Address
                Me.ListStaIncNo.Clear
                Do
                    .ListStaIncNo.AddItem Cl.Offset(0, 1).Value
                  
                    Set Cl = rSource.FindNext(Cl)
                Loop While Not Cl Is Nothing And Cl.Address <> ClAddress
            End If
        End With
    End Sub
    And here's the file.

    By the way, hope you've read the guidelines about forum cross posting.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    thank you so much. this works. i will mark the other post as solved here. again thank you.

Similar Threads

  1. Replies: 17
    Last Post: 05-22-2013, 11:58 PM
  2. Numbered List Of Unique Values
    By xander1981 in forum Excel Help
    Replies: 6
    Last Post: 01-21-2013, 06:10 PM
  3. Extract Unique Values List
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 4
    Last Post: 03-06-2012, 09:51 PM
  4. Replies: 2
    Last Post: 01-07-2012, 12:11 AM
  5. Extract Unique Values From a Range
    By Admin in forum Download Center
    Replies: 0
    Last Post: 05-13-2011, 10:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •