excel - Use a macro for sorting VBA -

i importing data txt file excel

how looks :

licence: md2 approval: granted id: 42fb1010c6b1ed82-0a292138.1 client: ch-w-7000361 username:asd  licence: cai approval: granted id: 42fb1010c6b1ed82-0a292138.1 client: ch-w-7000361 username:qwe 

so goes 1 multiple entries of same licence different approval's etc

what want is: filter data , give structure should like:

guidance on 'rule' how want order source data. once licence should appear

• licence
• approval (granted / not granted)
• id / client / username

how should like:

licence: edr approval: granted   client: pf-w-12365 approval: not granted   client: qt-b-7000361   client: c-s-7000361 

i have done pivot table automate macro

my code have done this

sub order() dim dickey string dim dicvalues string dim dic dim data  data = range("a1:d100") 'load data variable     creatobject("scripting.dictionary")         = 1 ubound(data)             if .exists(data(i, 1)) 'test see if key exists             else                 dickey = data(i, 1) 'set key                 dicvalues = data(i, 2) 'set value data stored                 .add dickey, dicvalues             end if              next             each dic in .keys                 if .items(dic) <> ""                 debug.print (dic)                 'do stuff                 end if             next dic         end end sub 

as alternative recommend load file using power query. it's free add in microsoft shipped natively excel 2013 , 2016 , can downloaded free excel 2010.

https://support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605

the tool allows set automatic query clean , shape data. can split text values, sort, filter, etc.

it more user friendly , produces more maintainable results.