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

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

<TBODY>
</TBODY>

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

<TBODY>
</TBODY>

 

[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