Insert Filter after text to column

[baha17]

Hi All,

I have a worksheet with 1000 rows with three columns. First column A is the employee numbers, column C is the employee skills as in below table1. I have a code which text to column those data. But I want them text to column in the order of table2.
Anyone can help me to give an idea?
Thank you very much for your kind attention.
Baha

     Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=False, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
        , 1)), TrailingMinusNumbers:=True
    Range("C:W").Select
    Selection.ColumnWidth = 6
    Range("C1:W1").Select
    Selection.AutoFilter

[TABLE="width: 539"]
<TBODY>[TR]
[TD]45445</SPAN>
[/TD]
[TD]Mark Jack</SPAN>
[/TD]
[TD]Nae1 Nvg5 bb5 ncb5 mb5 md5 mw5 sb5 </SPAN>
[/TD]
[/TR]
[TR]
[TD]45321</SPAN>
[/TD]
[TD]DHoney Poney</SPAN>
[/TD]
[TD]mb8 md8 bb8 sb5 shf5 bj6 yh5</SPAN>
[/TD]
[/TR]
[TR]
[TD]44854</SPAN>
[/TD]
[TD]LOU Chan Broh</SPAN>
[/TD]
[TD]Nvg5 ae5 bb6 ncb5 mb6 md6 mw5 sb5 </SPAN>
[/TD]
[/TR]
[TR]
[TD]46094</SPAN>
[/TD]
[TD]HUANG Gong</SPAN>
[/TD]
[TD]Nae1 Nvg1 Nbb1 Nncb1 Nmb1 Nmd1 Nmw1 Nsb1 </SPAN>
[/TD]
[/TR]
[TR]
[TD]45447</SPAN>
[/TD]
[TD]CHONG SIO Xiao</SPAN>
[/TD]
[TD]ncb5 mw5 sb5</SPAN>
[/TD]
[/TR]
[TR]
[TD]44765</SPAN>
[/TD]
[TD]LAM Luk Loi</SPAN>
[/TD]
[TD]sb5 bj5</SPAN>
[/TD]
[/TR]
[TR]
[TD]37816</SPAN>
[/TD]
[TD]TAM Yok king</SPAN>
[/TD]
[TD]Ro8 bj9 Nvg5 </SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 555"]
<TBODY>[TR]
[TD]TM#</SPAN></SPAN>
[/TD]
[TD]NAME</SPAN></SPAN>
[/TD]
[TD]Mb</SPAN></SPAN>
[/TD]
[TD]Ncb</SPAN></SPAN>
[/TD]
[TD]Bb</SPAN></SPAN>
[/TD]
[TD]Mw</SPAN></SPAN>
[/TD]
[TD]Bj</SPAN></SPAN>
[/TD]
[TD]Ro</SPAN></SPAN>
[/TD]
[TD]Sb</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]45445</SPAN>
[/TD]
[TD]Mark Jack</SPAN>
[/TD]
[TD]Mb5</SPAN></SPAN>
[/TD]
[TD]Ncb5</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sb5</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]45321</SPAN>
[/TD]
[TD]DHoney Poney</SPAN>
[/TD]
[TD]Mb8</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Bb8</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Bj6</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44854</SPAN>
[/TD]
[TD]LOU Chan Broh</SPAN>
[/TD]
[TD]Mb6</SPAN></SPAN>
[/TD]
[TD]Ncb5</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Mw5</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sb5</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]46094</SPAN>
[/TD]
[TD]HUANG Gong</SPAN>
[/TD]
[TD]Nmb1</SPAN></SPAN>
[/TD]
[TD]Nncb1</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Nmw1</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nsb1</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]45447</SPAN>
[/TD]
[TD]CHONG SIO Xiao</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44765</SPAN>
[/TD]
[TD]LAM Luk Loi</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bj5</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Sb5</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]37816</SPAN>
[/TD]
[TD]TAM Yok king</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bj9</SPAN></SPAN>
[/TD]
[TD]Ro8</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[MickG]

From your basic data in columns A, B & C, the Results start "F1"

[COLOR="Navy"]Sub[/COLOR] MG22Jan04
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Tab2
[COLOR="Navy"]Dim[/COLOR] oHd
[COLOR="Navy"]Dim[/COLOR] n       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Tab2 = Array("TM#", "NAME", "Mb", "Ncb", "Bb", "Mw", "Bj", "Ro", "Sb")
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
        ReDim ray(1 To Rng.Count, 1 To 9)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    oHd = Split(Dn, " ")
    c = c + 1
    ray(c, 1) = Dn.Offset(, -2): ray(c, 2) = Dn.Offset(, -1)
        [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Tab2)
            [COLOR="Navy"]For[/COLOR] Ac = 0 To UBound(oHd)
                [COLOR="Navy"]If[/COLOR] UCase(Tab2(n)) = UCase(Left(oHd(Ac), Len(Tab2(n)))) [COLOR="Navy"]Then[/COLOR]
                    ray(c, n + 1) = oHd(Ac)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
Range("F1").Resize(, 9).Value = Tab2
Range("F2").Resize(c, 9) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[baha17]

Mick,
What can I say, it works very very well. All I need now is just a little bit of arrangement.
Thank you very much and have a great day
baha