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

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?

Cross-posted at MS Excel, Access and VBA Discussion Forum

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

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?

---

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.

thank you so much. this works. i will mark the other post as solved here. again thank you.