3 related long lists in Comboboxes without Dublicate and Blanks, VBA

[moogeedoo]

Need help from excel masters,

I have a userform enclude 3 comboboxes, and I have 3 columns (A) for Branches (B) for Departments and (C) for Names A2:c5000 the 3 lists are related and has duplicate and some blanks, what I want to do that when I choose a branch name in first combobox I want to get all departments that has relation with this branch without duplicate and blanks, same after I choose one from departments I want to get all employees names that has relation with that branch and this Dept. in the last combobox, without Dups. and blanks, and kindly avoid array formulas it's take too much time because the size of data.

For example:

[TABLE="width: 380"]
<tbody>[TR]
[TD]Branch[/TD]
[TD]Dept.[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Lima[/TD]
[TD]Furniture & Interiors[/TD]
[TD]Jackson[/TD]
[/TR]
[TR]
[TD]Lima[/TD]
[TD]Food & Beverages[/TD]
[TD]Emma[/TD]
[/TR]
[TR]
[TD]Jakarta[/TD]
[TD]Real Estate Development[/TD]
[TD]Olivia[/TD]
[/TR]
[TR]
[TD]Dhaka[/TD]
[TD]Real Estate Development[/TD]
[TD]Lucas[/TD]
[/TR]
[TR]
[TD]Lagos[/TD]
[TD]Real Estate Development[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lagos[/TD]
[TD]Fashion wear[/TD]
[TD]Mia[/TD]
[/TR]
[TR]
[TD]Dhaka[/TD]
[TD]Fashion wear[/TD]
[TD]Caden[/TD]
[/TR]
[TR]
[TD]Karachi[/TD]
[TD]Motor Vehicles[/TD]
[TD]Jacob[/TD]
[/TR]
[TR]
[TD]Lagos[/TD]
[TD]Shopping Malls[/TD]
[TD]Logan[/TD]
[/TR]
[TR]
[TD]Lagos[/TD]
[TD]Shopping Malls[/TD]
[TD]Jayden[/TD]
[/TR]
[TR]
[TD]Lima[/TD]
[TD]Furniture & Interiors[/TD]
[TD]Madison[/TD]
[/TR]
[TR]
[TD]Dhaka[/TD]
[TD]Ceramics & Sanitary ware[/TD]
[TD]Ethan[/TD]
[/TR]
[TR]
[TD]Jakarta[/TD]
[TD]Restaurants & Cafés[/TD]
[TD]Emily[/TD]
[/TR]
[TR]
[TD]Jakarta[/TD]
[TD]Real Estate Development[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thank you for always support, and excuse me for lingual mistake

 

[Fluff]

How about

[COLOR=#ff0000]Option Explicit
Dim Dic As Object[/COLOR]

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.ComboBox2.List = Dic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.ComboBox3.List = Dic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim ws As Worksheet

   Set ws = Sheets("sheet1")
   Set Dic = CreateObject("Scripting.dictionary")
   For Each Cl In ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then
         Set Dic(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      If Not Dic(Cl.Value).Exists(Cl.Offset(, 1).Value) And Cl.Offset(, 1).Value <> "" Then
         Set Dic(Cl.Value)(Cl.Offset(, 1).Value) = CreateObject("scripting.dictionary")
      End If
      If Cl.Offset(, 2).Value <> "" Then
         Dic(Cl.Value)(Cl.Offset(, 1).Value)(Cl.Offset(, 2).Value) = Empty
      End If
   Next Cl
   Me.ComboBox1.List = Dic.Keys
End Sub

The 2 lines in red must go at the very top of the module, before any code.

 

[moogeedoo]

How about

[COLOR=#ff0000]Option Explicit
Dim Dic As Object[/COLOR]

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.ComboBox2.List = Dic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.ComboBox3.List = Dic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim ws As Worksheet

   Set ws = Sheets("sheet1")
   Set Dic = CreateObject("Scripting.dictionary")
   For Each Cl In ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then
         Set Dic(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      If Not Dic(Cl.Value).Exists(Cl.Offset(, 1).Value) And Cl.Offset(, 1).Value <> "" Then
         Set Dic(Cl.Value)(Cl.Offset(, 1).Value) = CreateObject("scripting.dictionary")
      End If
      If Cl.Offset(, 2).Value <> "" Then
         Dic(Cl.Value)(Cl.Offset(, 1).Value)(Cl.Offset(, 2).Value) = Empty
      End If
   Next Cl
   Me.ComboBox1.List = Dic.Keys
End Sub

The 2 lines in red must go at the very top of the module, before any code.

thank U Fluff for quick reply, but unfortunately I insert the code in the userform module but nothing happened all combobox is empty. and I try with it by change a little as much as I know but also nothing changed.

what I should do??

 

[Fluff]

Forgot to mention, you need to change this to match your sheet name

Set ws = Sheets("[COLOR=#ff0000]sheet1[/COLOR]")

Also are you combos called ComboBox1 ComboBox2 etc?

 

[moogeedoo]

What Amazing R U???, It's happened I tryed it with a small that I sent it's working may be something wrong in my sheet I will review my codes and try to enroll your code between my code lines..
I cannot believe about 3 days I am running behind this,,,,,, Thank you teacher FLUFFY

 

[Fluff]

You're welcome & thanks for the feedback

 

[moogeedoo]

Hi Mr. Fluff, Sorry for inconvenience, Do you still remember my inquire about 3 related lists in comboboxes, I just need a modification if you can, I want to do the same Lists but not in comboboxes in cells in sheet, what I mean I want each cell in Columns G & H & I. has validation list.

 

[Fluff]

As this is a totally different question, you will need to start a new thread.

 

[moogeedoo]

Thank you for your kind support and now I am posting new thread, hope to find Solution