Formula or Macro to Organize a List of Data into a Table for a Dependent Dropdown List?

[MorgWalker]

Hi Everyone,

I'm trying to organize my data so that it can be used in a dependent dropdown list.

I can't figure out how to do this. I can't do it manually because there are 100+ rows that need to be converted. I've tried transposing it, as well as creating a pivot table, but neither option seems to work. I've googled other peoples macros in excel for similar situations, but everyone seems to have slightly different data or requirements than I do, so their code doesn't work for me.

My data is organized like this into two columns:

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]






















Each row has to be "flipped" so that each unique row in the first column becomes a header in a column, and all the data associated with it is listed under the header. Notice that some items such as "D" and "H" have no data associated with them, but I need them incorporated as well. Also notice that the data is not 1:1 (e.g. B,2 and G,2)

I need the data to be arranged like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









Is there a formula or a macro that can help arrange the data to be this way?

If anything is unclear, please let me know.

Thank you.

 

[MickG]

Try this or results on sheet2.

[COLOR=navy]Sub[/COLOR] MG20Aug01
[COLOR=navy]Dim[/COLOR] myArraylist [COLOR=navy]As[/COLOR] Object, Dic [COLOR=navy]As[/COLOR] Object, Q [COLOR=navy]As[/COLOR] Variant, oMax [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=navy]Set[/COLOR] myArraylist = CreateObject("System.Collections.ArrayList")
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        Dic.Add Dn.Value, Array(Dn.Offset(, 1), 1)
    [COLOR=navy]Else[/COLOR]
      Q = Dic(Dn.Value)
        Q(0) = Q(0) & "," & Dn.Offset(, 1)
        Q(1) = Q(1) + 1
        oMax = Application.Max(oMax, Q(1))
      Dic(Dn.Value) = Q
    [COLOR=navy]End[/COLOR] If
    [COLOR=navy]If[/COLOR] Not myArraylist.CONTAINS(Dn.Value) [COLOR=navy]Then[/COLOR]
        myArraylist.Add Dn.Value 
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
myArraylist.Sort
ReDim nray(1 To oMax + 1, 1 To myArraylist.Count)
[COLOR=navy]For[/COLOR] n = 0 To myArraylist.Count - 1
    Sp = Split(Dic(myArraylist(n))(0), ",")
        nray(1, n + 1) = myArraylist(n)
        [COLOR=navy]For[/COLOR] Rw = 0 To UBound(Sp)
            nray(Rw + 2, n + 1) = Sp(Rw)
        [COLOR=navy]Next[/COLOR] Rw
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(oMax + 1, myArraylist.Count)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[MorgWalker]

MickG, this works perfectly.

Thank you!

 

[MickG]

You're very welcome
Regrds Mick