How do I create COMPLEX indirect data validation lists...

[calumbus53]

Hi guys, please help...

I am trying to create a series of indirect lists but only lists that link to their relative tree.

If say for example Parent "a" was selected, I would only have access to values directly linked with "a" (such as "a1.1, a1.2, a1.3, a1.4, a1.4", "a2.1, a2.2" and so on) and then on into child 2, repeating the same method. If "b" was selected, I only want to be able to select "b" related children, and then again onto the next level down.

I have managed to do this by creating lots of indirect lists but it is manual and just created over a "drag n drop" array! If the data is updated, I'm screwed!

Do you have any ideas? / Fancy help me doing it?

Below is an example of the format I am working to:

[TABLE="width: 730"]
<tbody>[TR]
[TD][/TD]
[TD]Parent Selection[/TD]
[TD]1 Child Selection[/TD]
[TD]2 Child Selection[/TD]
[TD][/TD]
[TD][/TD]
[TD]Parent[/TD]
[TD][/TD]
[TD]1st Child[/TD]
[TD][/TD]
[TD]2 Child[/TD]
[/TR]
[TR]
[TD]10.02.02[/TD]
[TD]a[/TD]
[TD]a2.1[/TD]
[TD]aa2.1[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10.03.03[/TD]
[TD]a[/TD]
[TD]a3.1[/TD]
[TD]aa3.1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]01[/TD]
[TD]a1.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11.02.02[/TD]
[TD]b[/TD]
[TD]b2.2[/TD]
[TD]bb2.1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a1.2[/TD]
[TD]01[/TD]
[TD]aa1.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a1.3[/TD]
[TD][/TD]
[TD]aa1.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a1.4[/TD]
[TD][/TD]
[TD]aa1.3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a1.5[/TD]
[TD] [/TD]
[TD]aa1.4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]02[/TD]
[TD]a2.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a2.2[/TD]
[TD]02[/TD]
[TD]aa2.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]03[/TD]
[TD]a3.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a3.2[/TD]
[TD]03[/TD]
[TD]aa3.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a3.3[/TD]
[TD] [/TD]
[TD]aa3.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]04[/TD]
[TD]a4.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]a4.2[/TD]
[TD]04[/TD]
[TD]aa4.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD]01[/TD]
[TD]b1.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD] [/TD]
[TD]b1.2[/TD]
[TD]01[/TD]
[TD]bb1.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD]02[/TD]
[TD]b2.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD][/TD]
[TD]b2.2[/TD]
[TD]02[/TD]
[TD]bb2.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD] [/TD]
[TD]b2.3[/TD]
[TD] [/TD]
[TD]bb2.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD]03[/TD]
[TD]b3.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD] [/TD]
[TD]b3.2[/TD]
[TD]03[/TD]
[TD]bb3.1[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[MickG]

Cal
The best option for your piece of Code (index, Match) is to start a new thread. I should include an example of the related Data and your expected results, that way you get the full benefit of the Board.

Regarding my code:-
Q(1) I'm unable to rename the sheet or copy the code across to work in my form.
A(1) If your form has the same controls as Mine (comboboxes 1 to 3 and Text boxes 1 to 2) then you can do a straight copy and paste into your Userform Module.
The code is based on the active sheet so if your data is on another sheet from the Userform sheet you will need to alter the code as below:-[

Dim nStr As String
[B][COLOR=#b22222]With sheet("Mysheet")
Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
end with[/COLOR][/B]
Set Dic = CreateObject("Scripting.Dictionary")

Q(2) I need to add in a 4th dependency combo box.
A(2) Combobox3.values are unique so "Results code" (4th Dependency) column are unique and Pasted in "Results code" Textbox. unless you wanted to include a "Blank" ,Thereby returning "Results code" like "20,01,00" and "20,02,00" ????

Q(3)When deleting one of the dependencies I need the full code box to reset or better still, say for example: (if it was) 11.01.01 and then the final combo box was cleared, (it should say) 11.01.00 Or if one of the dependencies was cleared, it should clear the code.
A(3) When you say "Delete" do you mean actually deleting the value rather than selecting from the Combobox????
I have altered the code (Below) so that any deleting should remove further down the line values.

Paste the whole of this code into your Userform Module. (at the top of code module)

Option Explicit
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant
Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
'[COLOR=green][B]Trees/2[/B][/COLOR]
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Ps [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Gs [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] temp(1 To 3)
[COLOR=navy]Dim[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Dn.Value <> "" [COLOR=navy]Then[/COLOR]
    [COLOR=navy]If[/COLOR] Dn.Offset(, 1) <> "" [COLOR=navy]Then[/COLOR] temp(1) = Dn.Offset(, 1).Value
    [COLOR=navy]If[/COLOR] Dn.Offset(, 3) <> "" [COLOR=navy]Then[/COLOR] temp(2) = Dn.Offset(, 3).Value
    temp(3) = Dn.Offset(, 5).Value
    
    [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
            [COLOR=navy]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
        [COLOR=navy]End[/COLOR] If
            [COLOR=navy]If[/COLOR] Dn.Offset(, 2).Value <> "" [COLOR=navy]Then[/COLOR]
                [COLOR=navy]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 2).Value) [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]Set[/COLOR] Dic(Dn.Value)(Dn.Offset(, 2).Value) = CreateObject("Scripting.Dictionary")
                    Dic(Dn.Value)(Dn.Offset(, 2).Value).CompareMode = 1
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]End[/COLOR] If
              [COLOR=navy]If[/COLOR] Dn.Offset(, 4).Value = "" And Dn.Offset(1, 4).Value <> "" [COLOR=navy]Then[/COLOR]
                    Dic(Dn.Value)(Dn.Offset(, 2).Value)(Dn.Offset(1, 4).Value) = Array(temp(1), temp(2), temp(3), Dn.Offset(1))
              [COLOR=navy]ElseIf[/COLOR] Dn.Offset(, 4).Value <> "" [COLOR=navy]Then[/COLOR]
                    Dic(Dn.Value)(Dn.Offset(, 2).Value)(Dn.Offset(, 4).Value) = Array(temp(1), temp(2), temp(3), Dn)
               [COLOR=navy]End[/COLOR] If
        [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] Dic.keys
    nStr = nStr & IIf(nStr = "", K, "," & K)
[COLOR=navy]Next[/COLOR] K
ComboBox1.Clear
ComboBox2.Clear
ComboBox3.Clear
TextBox1.Text = ""
TextBox2.Text = ""
ComboBox1.List = Split(nStr, ",")
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] P [COLOR=navy]As[/COLOR] Variant, nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] G [COLOR=navy]As[/COLOR] Variant
    [COLOR=navy]If[/COLOR] Dic.exists(ComboBox1.Value) [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] P [COLOR=navy]In[/COLOR] Dic(ComboBox1.Value).keys
           nStr = nStr & IIf(nStr = "", P, "," & P)
    [COLOR=navy]Next[/COLOR] P
    [COLOR=navy]With[/COLOR] ComboBox2
        .Clear
        .Value = ""
        .List = Split(nStr, ",")
    [COLOR=navy]End[/COLOR] With
ComboBox3.Clear
[COLOR=navy]Else[/COLOR]
ComboBox2.Value = ""
ComboBox3.Value = ""
TextBox1.Text = ""
TextBox2.Text = ""
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] ComboBox2_Change()
[COLOR=navy]Dim[/COLOR] G [COLOR=navy]As[/COLOR] Variant, nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]If[/COLOR] ComboBox1.Value <> "" And ComboBox2.Value <> "" [COLOR=navy]Then[/COLOR]
[COLOR=navy]If[/COLOR] Dic(ComboBox1.Value).exists(ComboBox2.Value) [COLOR=navy]Then[/COLOR]
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] G [COLOR=navy]In[/COLOR] Dic(ComboBox1.Value)(ComboBox2.Value).keys
            nStr = nStr & IIf(nStr = "", G, "," & G)
        [COLOR=navy]Next[/COLOR] G
        
        [COLOR=navy]With[/COLOR] ComboBox3
            .Value = ""
            .Clear
            .List = Split(nStr, ",")
        [COLOR=navy]End[/COLOR] With
[COLOR=navy]Else[/COLOR]
'[COLOR=green][B]ComboBox1.Clear[/B][/COLOR]
'[COLOR=green][B]ComboBox2.Clear[/B][/COLOR]
TextBox1.Text = ""
TextBox2.Text = ""
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] ComboBox3_Change()
[COLOR=navy]If[/COLOR] ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3 <> "" [COLOR=navy]Then[/COLOR]
[COLOR=navy]If[/COLOR] Dic(ComboBox1.Value)(ComboBox2.Value).exists(ComboBox3.Value) [COLOR=navy]Then[/COLOR]
TextBox1.Text = Format(Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value)(0) & "." & _
                Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value)(1) & "." & _
                Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value)(2), "hh.mm.ss")
TextBox2.Text = Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value)(3).Offset(, 6)
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Else[/COLOR]
TextBox1.Text = ""
TextBox2.Text = ""
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[calumbus53]

Thank you very much for your help Mick

 

[calumbus53]

Hi Mick,

I am struggling. I have used the code you very kindly helped put together for me. However, I am unable to do the following:

1) Introduce a forth combo box, which is dependant on the 3rd (I have further detail - i.e. Tree1, Tree2, Tree3, Tree4)
2) The Trees have associated numeric values for the result code from the second Tree down to the forth (Tree1 has NO associated number for the result code.
3) If Tree1 was selected we can then select the dependant value from Tree2 and if it's resulting value was "11" then the result box would display "11.00.00" - if we then selected the 1st value in Tree3, we would have "11.01.00" and then in the forth tree (Tree4), we would select the 1st dependency value and see in the results box "11.01.01".

I have tried to edit your code but I am only introducing Errors.

Thank you in advance.

 

[MickG]

Try this !!!
https://app.box.com/s/90knwfgdah2azvg2v00nwxu6dw4s58ls

 

[calumbus53]

There should be Tree1 data which leads to Tree2 > Tree3 > Tree4
as below:

The amount of data in the table I am working with is enormous. Tree1 data "Body and Safety" (after some rows) eventually becomes "Trim and Mechanisms" and then this has all its dependencies. Tree1 just does not have a associated code which is fine.

Everything you have helped with is great! - I just need to introduce the forth dependency.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 1028"]
<tbody>[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD]23[/TD]
[TD]Seatbelts[/TD]
[TD]00[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]23.00.00[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Seatbelts[/TD]
[TD]01[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]23.01.00[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Seatbelts[/TD]
[TD][/TD]
[TD]Seat Belt Assembly Front Row[/TD]
[TD]01[/TD]
[TD]23.01.01[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Seatbelts[/TD]
[TD][/TD]
[TD]Seat Belts - Second Row[/TD]
[TD]02[/TD]
[TD]23.01.02[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Seatbelts[/TD]
[TD][/TD]
[TD]Seat Belts - Additional Rows[/TD]
[TD]03[/TD]
[TD]23.01.03[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Seatbelts[/TD]
[TD][/TD]
[TD]Child Tethers[/TD]
[TD]04[/TD]
[TD]23.01.04[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Passenger & Knee Airbag[/TD]
[TD]02[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]23.02.00[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Passenger & Knee Airbag[/TD]
[TD][/TD]
[TD]Passenger Airbag[/TD]
[TD]01[/TD]
[TD]23.02.01[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Passenger & Knee Airbag[/TD]
[TD][/TD]
[TD]Inflatable Knee Bolster[/TD]
[TD]02[/TD]
[TD]23.02.02[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Side Bags[/TD]
[TD]03[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]23.03.00[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Side Bags[/TD]
[TD][/TD]
[TD]Front Side Air Bag[/TD]
[TD]01[/TD]
[TD]23.03.01[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Side Bags[/TD]
[TD][/TD]
[TD]Curtain Airbag System[/TD]
[TD]02[/TD]
[TD]23.03.02[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Side Bags[/TD]
[TD][/TD]
[TD]Door Mounted Inflatable Curtain[/TD]
[TD]03[/TD]
[TD]23.03.03[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Side Bags[/TD]
[TD][/TD]
[TD]Rear Side Air Bag[/TD]
[TD]04[/TD]
[TD]23.03.04[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Safety Electronics[/TD]
[TD]04[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]23.04.00[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Safety Electronics[/TD]
[TD][/TD]
[TD]Restraining Devices, Actuation Sensing[/TD]
[TD]01[/TD]
[TD]23.04.01[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Safety Electronics[/TD]
[TD][/TD]
[TD]Occupant Classification System[/TD]
[TD]02[/TD]
[TD]23.04.02[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Safety Electronics[/TD]
[TD][/TD]
[TD]Belt Minder Sensor[/TD]
[TD]03[/TD]
[TD]23.04.03[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Safety Electronics[/TD]
[TD][/TD]
[TD]Occupants Restraints Controller[/TD]
[TD]04[/TD]
[TD]23.04.04[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Steering Wheel and Drivers Airbag[/TD]
[TD]05[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]23.05.00[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD="colspan: 2"]Steering Wheel and Drivers Airbag[/TD]
[TD]Steering Wheel[/TD]
[TD]01[/TD]
[TD]23.05.01[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD="colspan: 2"]Steering Wheel and Drivers Airbag[/TD]
[TD]Drivers Air Bag[/TD]
[TD]02[/TD]
[TD]23.05.02[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD="colspan: 2"]Steering Wheel and Drivers Airbag[/TD]
[TD]Drivers Air Bag Bezel[/TD]
[TD]03[/TD]
[TD]23.05.03[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Active Safety (ADAS)[/TD]
[TD]06[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]23.06.00[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Active Safety (ADAS)[/TD]
[TD][/TD]
[TD]Front Safety Sensing [/TD]
[TD]01[/TD]
[TD]23.06.01[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Active Safety (ADAS)[/TD]
[TD][/TD]
[TD]Rear Safety Sensing [/TD]
[TD]02[/TD]
[TD]23.06.02[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Active Safety (ADAS)[/TD]
[TD][/TD]
[TD]Forward Facing Camera[/TD]
[TD]03[/TD]
[TD]23.06.03[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Active Safety (ADAS)[/TD]
[TD][/TD]
[TD]Central ADAS Controller[/TD]
[TD]04[/TD]
[TD]23.06.04[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD]Miscellaneous Safety Components[/TD]
[TD]07[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]23.07.00[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD="colspan: 2"]Miscellaneous Safety Components[/TD]
[TD]Deployable Roll Bar Systems[/TD]
[TD]01[/TD]
[TD]23.07.01[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD="colspan: 2"]Miscellaneous Safety Components[/TD]
[TD]Pedestrian Protection Deployment[/TD]
[TD]02[/TD]
[TD]23.07.02[/TD]
[/TR]
[TR]
[TD]Body and Safety[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD][/TD]
[TD="colspan: 2"]Miscellaneous Safety Components[/TD]
[TD]Side Impact Protection - Door[/TD]
[TD]03[/TD]
[TD]23.07.03[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]00[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]30.00.00[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD]01[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]30.01.00[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Front Seat Frame Assy[/TD]
[TD]01[/TD]
[TD]30.01.01[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Front Seat Trim[/TD]
[TD]02[/TD]
[TD]30.01.02[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Front Seat Comfort[/TD]
[TD]03[/TD]
[TD]30.01.03[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Front Seat Covers[/TD]
[TD]04[/TD]
[TD]30.01.04[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Front Seat Headrest[/TD]
[TD]05[/TD]
[TD]30.01.05[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Second row Seat Frame Assy[/TD]
[TD]06[/TD]
[TD]30.01.06[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Second row Seat Trim[/TD]
[TD]07[/TD]
[TD]30.01.07[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Second row Seat Comfort[/TD]
[TD]08[/TD]
[TD]30.01.08[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Second row Seat Covers[/TD]
[TD]09[/TD]
[TD]30.01.09[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Second row Seat Headrest[/TD]
[TD]10[/TD]
[TD]30.01.10[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Third row Seat Frame Assy[/TD]
[TD]11[/TD]
[TD]30.01.11[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Third row Seat Trim[/TD]
[TD]12[/TD]
[TD]30.01.12[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Third row Seat Comfort[/TD]
[TD]13[/TD]
[TD]30.01.13[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Third row Seat Covers[/TD]
[TD]14[/TD]
[TD]30.01.14[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Third row Seat Headrest[/TD]
[TD]15[/TD]
[TD]30.01.15[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Seating[/TD]
[TD][/TD]
[TD]Seat ECU and Software[/TD]
[TD]16[/TD]
[TD]30.01.16[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]NVH and Heat Protection[/TD]
[TD]02[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]30.02.00[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]NVH and Heat Protection[/TD]
[TD][/TD]
[TD]Trim & Final Baffles[/TD]
[TD]01[/TD]
[TD]30.02.01[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]NVH and Heat Protection[/TD]
[TD][/TD]
[TD]Encapsulation NVH[/TD]
[TD]02[/TD]
[TD]30.02.02[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]NVH and Heat Protection[/TD]
[TD][/TD]
[TD]NVH Interior[/TD]
[TD]03[/TD]
[TD]30.02.03[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]NVH and Heat Protection[/TD]
[TD][/TD]
[TD]NVH Exterior[/TD]
[TD]04[/TD]
[TD]30.02.04[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]NVH and Heat Protection[/TD]
[TD][/TD]
[TD]Front Wheel Arch Liners[/TD]
[TD]05[/TD]
[TD]30.02.05[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]NVH and Heat Protection[/TD]
[TD][/TD]
[TD]Rear Wheel Arch Liners[/TD]
[TD]06[/TD]
[TD]30.02.06[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Lower Environment[/TD]
[TD]03[/TD]
[TD][/TD]
[TD]00[/TD]
[TD]30.03.00[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Lower Environment[/TD]
[TD][/TD]
[TD]Main Floor Trim[/TD]
[TD]01[/TD]
[TD]30.03.01[/TD]
[/TR]
[TR]
[TD]Trim and Mechanisms[/TD]
[TD][/TD]
[TD]Interior Trim[/TD]
[TD][/TD]
[TD]Lower Environment[/TD]
[TD][/TD]
[TD]Sill Trim and Name Plate[/TD]
[TD]02[/TD]
[TD]30.03.02[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Can I now assume from your latest Table, that the 4 Comboboxes are for "Tree1 to Tree4" and Textbox1 is for the Results "10:01:01" (Ex Cols 4,6 & 8) etc. and Textbox2 is for the "Results code" !!!!

 

[MickG]

Is your latest table an accurate representation of your data because it now appears to have merged cells, which will need to removed for the code to work. The table also , now has no blank cells in column 3, which need to be accounted for.

It is very important that the data you sent accurately represents the true data !!!

 

[calumbus53]

Mick,

Is there a way I can send you a file / screenshot? I am struggling to communicate directly through the forum.

I appreciate this is probably getting frustrating for you.

Cheers,

Cal

 

[MickG]

Try:-
https://www.box.com/en-gb/home

 

[calumbus53]

Hi Mick,

I am stuck at the bottom of this code... I think I need to do something at the top to edit the .key or .lists?? - I am however not too sure? I have been trying to play with it but I am really struggling.

Option Explicit
Dim Dic As Object
Dim K As Variant
Private Sub UserForm_Initialize()
'Trees/2
Dim Rng As Range, Dn As Range
Dim c As Long
Dim Ps As String
Dim Gs As String
Dim temp(1 To 4)
Dim nStr As String
With Sheets("Sheet_CPSC")
Set Rng = .Range("A3", .Range("A" & Rows.Count).End(xlUp))
End With
Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
    For Each Dn In Rng
    If Dn.Value <> "" Then
    If Dn.Offset(, 1) <> "" Then temp(1) = Dn.Offset(, 1).Value
    If Dn.Offset(, 3) <> "" Then temp(2) = Dn.Offset(, 3).Value
    If Dn.Offset(, 5) <> "" Then temp(3) = Dn.Offset(, 5).Value
    temp(4) = Dn.Offset(, 7).Value
    If Not Dic.exists(Dn.Value) Then
            Set Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
        End If
            If Dn.Offset(, 2).Value <> "" Then
                If Not Dic(Dn.Value).exists(Dn.Offset(, 2).Value) Then
                    Set Dic(Dn.Value)(Dn.Offset(, 2).Value) = CreateObject("Scripting.Dictionary")
                    Dic(Dn.Value)(Dn.Offset(, 2).Value).CompareMode = 1
                End If
            End If
              If Dn.Offset(, 4).Value = "" And Dn.Offset(1, 4).Value <> "" Then
                    Dic(Dn.Value)(Dn.Offset(, 2).Value)(Dn.Offset(1, 4).Value) = Array(temp(1), temp(2), temp(3), temp(4), Dn.Offset(1))
              ElseIf Dn.Offset(, 4).Value <> "" Then
                    Dic(Dn.Value)(Dn.Offset(, 2).Value)(Dn.Offset(, 4).Value) = Array(temp(1), temp(2), temp(3), temp(4), Dn)
               End If
        End If
        Next Dn
For Each K In Dic.keys
    nStr = nStr & IIf(nStr = "", K, "," & K)
Next K
ComboBox1.Clear
ComboBox2.Clear
ComboBox3.Clear
ComboBox4.Clear
txtCPSC.Text = ""
'txtCPSC.Text = ""
ComboBox1.List = Split(nStr, ",")
End Sub
Private Sub ComboBox1_Change()
Dim P As Variant, nStr As String, G As Variant
    If Dic.exists(ComboBox1.Value) Then
    For Each P In Dic(ComboBox1.Value).keys
           nStr = nStr & IIf(nStr = "", P, "," & P)
    Next P
    With ComboBox2
        .Value = ""
        .Clear
        .List = Split(nStr, ",")
    End With
ComboBox3.Clear
Else
ComboBox2.Value = ""
ComboBox3.Value = ""
txtCPSC.Text = ""
'txtCPSC.Text = ""
End If
End Sub
Private Sub ComboBox2_Change()
Dim G As Variant, nStr As String
If ComboBox1.Value <> "" And ComboBox2.Value <> "" Then
        For Each G In Dic(ComboBox1.Value)(ComboBox2.Value).keys
            nStr = nStr & IIf(nStr = "", G, "," & G)
        Next G
        With ComboBox3
            .Value = ""
            .Clear
            .List = Split(nStr, ",")
        End With
Else
'ComboBox1.Clear
'ComboBox2.Clear
txtCPSC.Text = ""
'txtCPSC.Text = ""
End If
End Sub
Private Sub ComboBox3_Change()
Dim H As Variant, nStr As String
If ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3 <> "" Then
        For Each H In Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value).keys
            nStr = nStr & IIf(nStr = "", H, "," & H)
        Next H
        With ComboBox4
            .Value = ""
            .Clear
            .List = Split(nStr, ",")
        End With
If Dic(ComboBox1.Value)(ComboBox2.Value).exists(ComboBox3.Value) Then
txtCPSC.Text = Format(Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value)(0) & "." & _
                Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value)(1) & "." & _
                Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value)(2), "hh.mm.ss")
'txtCPSC.Text = Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value)(3).Offset(, 6)
End If
Else
txtCPSC.Text = ""
'txtCPSC.Text = ""
End If
End Sub
Private Sub ComboBox4_Change()
End Sub