sorting in multiple data patterns

[theguruguy]

my problem is that i have a list of user id's in column A. in columns B-? are permissions associated with that user ID.
I need to go through every row and sort by permission that would fit in a similar category. For example if a column value starts with grp.app.xxx
I would want to move all those to the same column on a different sheet. that new header column could be called apps.
then it continues is evaluation of the rows, doing the same thing for grp.ctx.xxx, moving to a new column on the new sheet with the header citrix, and again from grp.share.xxx. ect,ect. so columns could have more than one value in them so the script would have to append the data. most importantly keeping the data associated with the user.

after it finishes the row it would need to go to next row and do it again. about 1000 user id's

after is is all over it would be great to sort the columns with multiple values alphabetically..

im so lost on this.

 

[alansidman]

Perhaps the simplest way would be to use the built in Filter function on the Data tab. Filter column B for each category and then copy and paste the filtered values to a new column. Repeat for each category.

 

[theguruguy]

this is a lot of entries, that would take a lot of time.

 

[alansidman]

Post a sample of what your worksheet looks like and I am sure someone here will create a VBA solution for you or a Power Query solution. Make sure that what you post is representative of your actual worksheet and contains enough records to be realistic, ie. 8-10 records.

 

[theguruguy]

Post a sample of what your worksheet looks like and I am sure someone here will create a VBA solution for you or a Power Query solution. Make sure that what you post is representative of your actual worksheet and contains enough records to be realistic, ie. 8-10 records.

so this will be 1000 or so users long. and could be as many as 50 random groups assigned to each user. the letters after the second period in each random group will not have any consistency.. consistency ends after second period. for example instead of being grp.app.app7 it would be grp.app.youneverheardofthisapp

user Id	random groups	random groups	random groups	random groups
user1	grp.ctx.ctx1	grp.share.share100	grp.app.app7	grp.app.app15
user2	grp.ctx.ctx10	grp.vdi.vdi20	grp.app.app10	grp.app.app2

<tbody>
</tbody>

I want the end result on a new worksheets like what i have below.

user ID	Applications	VDI	Fileshare	Citrix
user1	grp.app.app7, grp.app.app15		grp.share.share100	grp.ctx.ctx1
user2	grp.app.app10, grp.app.app2	grp.vdi.vdi20		grp.ctx.ctx10

<tbody>
</tbody>

as you can see there could be many entries for each user in several of the columns. so i would need data to append and not overwrite.
and it would be really nice to sort the data in the cells alphabetical once the matching and copying has been accomplished.

thank you

 

[alansidman]

I am not at home and do not have full Excel capabilities, but looking at what you want, I believe can be achieved by using Power Query to Unpivot the current data. Then it will be a case of filtering the data in place with either Power Query or VBA. I will look at this later when I get home unless someone else steps in.

 

[alansidman]

first, we need to unpivot the data.

Open Power Query/Get and Transform. Click on New Query.

Open blank query in the editor, launch Advanced Editor and paste in the following code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"user Id", type text}, {"random groups", type text}, {"random groups2", type text}, {"random groups3", type text}, {"random groups4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"user Id"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

With your data unpivoted, you can now filter the data on each of the categories, share, citrix, etc and copy and paste into the appropriate columns

 

[Akuini]

Here's another option, using VBA:
I put the result in sheet2

[B][COLOR=Royalblue]Sub[/COLOR][/B] a1080939a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1080939-sorting-multiple-data-patterns.html[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] d [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Object[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] a [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], b [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], m [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], n [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], j [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], k [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] z [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]String[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] va [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], vb [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], vc [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], q [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B]

a = Range([COLOR=brown]"A"[/COLOR] & Rows.count).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row
b = Cells.Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
va = Range(Cells([COLOR=crimson]2[/COLOR], [COLOR=brown]"B"[/COLOR]), Cells(a, b))

[B][COLOR=Royalblue]Set[/COLOR][/B] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
    d.CompareMode = vbTextCompare
    [B][COLOR=Royalblue]For[/COLOR][/B] [B][COLOR=Royalblue]Each[/COLOR][/B] x [B][COLOR=Royalblue]In[/COLOR][/B] va
        [B][COLOR=Royalblue]If[/COLOR][/B] Len(x) > [COLOR=crimson]4[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            m = InStr([COLOR=crimson]1[/COLOR], x, [COLOR=brown]"."[/COLOR])
            n = InStr(m + [COLOR=crimson]1[/COLOR], x, [COLOR=brown]"."[/COLOR])
            z = [B][COLOR=Royalblue]Mid[/COLOR][/B](x, m, n - m + [COLOR=crimson]1[/COLOR])
            d(z) = [COLOR=brown]""[/COLOR]
        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]

[B][COLOR=Royalblue]ReDim[/COLOR][/B] vb([COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] d.count)

[B][COLOR=Royalblue]For[/COLOR][/B] [B][COLOR=Royalblue]Each[/COLOR][/B] q [B][COLOR=Royalblue]In[/COLOR][/B] d.Keys
    i = i + [COLOR=crimson]1[/COLOR]: vb([COLOR=crimson]1[/COLOR], i) = q
[B][COLOR=Royalblue]Next[/COLOR][/B]

[B][COLOR=Royalblue]ReDim[/COLOR][/B] vc([COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(vb, [COLOR=crimson]2[/COLOR]))

[B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR])
    [B][COLOR=Royalblue]For[/COLOR][/B] j = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]2[/COLOR])
        [B][COLOR=Royalblue]For[/COLOR][/B] k = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(vb, [COLOR=crimson]2[/COLOR])
            [B][COLOR=Royalblue]If[/COLOR][/B] InStr([COLOR=crimson]1[/COLOR], va(i, j), vb([COLOR=crimson]1[/COLOR], k), [COLOR=crimson]1[/COLOR]) [B][COLOR=Royalblue]Then[/COLOR][/B]
                [B][COLOR=Royalblue]If[/COLOR][/B] vc(i, k) = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
                    vc(i, k) = va(i, j)
                    [B][COLOR=Royalblue]Else[/COLOR][/B]
                    vc(i, k) = vc(i, k) & [COLOR=brown]", "[/COLOR] & va(i, j)
                [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
            [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
        [B][COLOR=Royalblue]Next[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
[B][COLOR=Royalblue]Next[/COLOR][/B]

va = Range(Cells([COLOR=crimson]1[/COLOR], [COLOR=brown]"A"[/COLOR]), Cells(a, [COLOR=brown]"A"[/COLOR]))

[B][COLOR=Royalblue]With[/COLOR][/B] Sheets([COLOR=brown]"Sheet2"[/COLOR])
.Range([COLOR=brown]"A1"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = va
.Range([COLOR=brown]"B1"[/COLOR]).Resize([COLOR=crimson]1[/COLOR], UBound(vb, [COLOR=crimson]2[/COLOR])) = vb
.Range([COLOR=brown]"B2"[/COLOR]).Resize(UBound(vc, [COLOR=crimson]1[/COLOR]), UBound(vc, [COLOR=crimson]2[/COLOR])) = vc
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

 

[theguruguy]

first, we need to unpivot the data.

Open Power Query/Get and Transform. Click on New Query.

Open blank query in the editor, launch Advanced Editor and paste in the following code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"user Id", type text}, {"random groups", type text}, {"random groups2", type text}, {"random groups3", type text}, {"random groups4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"user Id"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

With your data unpivoted, you can now filter the data on each of the categories, share, citrix, etc and copy and paste into the appropriate columns

I get the following error when i run the query... please excuse me as i am very new to this.

Expression.Error: We couldn't find an Excel table named 'Table1'.Details:
Table1

 

[theguruguy]

Here's another option, using VBA:
I put the result in sheet2

[B][COLOR=Royalblue]Sub[/COLOR][/B] a1080939a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1080939-sorting-multiple-data-patterns.html[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] d [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Object[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] a [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], b [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], m [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], n [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], j [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B], k [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] z [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]String[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] va [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], vb [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], vc [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B], q [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B]

a = Range([COLOR=brown]"A"[/COLOR] & Rows.count).[B][COLOR=Royalblue]End[/COLOR][/B](xlUp).Row
b = Cells.Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
va = Range(Cells([COLOR=crimson]2[/COLOR], [COLOR=brown]"B"[/COLOR]), Cells(a, b))

[B][COLOR=Royalblue]Set[/COLOR][/B] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
    d.CompareMode = vbTextCompare
    [B][COLOR=Royalblue]For[/COLOR][/B] [B][COLOR=Royalblue]Each[/COLOR][/B] x [B][COLOR=Royalblue]In[/COLOR][/B] va
        [B][COLOR=Royalblue]If[/COLOR][/B] Len(x) > [COLOR=crimson]4[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
            m = InStr([COLOR=crimson]1[/COLOR], x, [COLOR=brown]"."[/COLOR])
            n = InStr(m + [COLOR=crimson]1[/COLOR], x, [COLOR=brown]"."[/COLOR])
            z = [B][COLOR=Royalblue]Mid[/COLOR][/B](x, m, n - m + [COLOR=crimson]1[/COLOR])
            d(z) = [COLOR=brown]""[/COLOR]
        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]

[B][COLOR=Royalblue]ReDim[/COLOR][/B] vb([COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] d.count)

[B][COLOR=Royalblue]For[/COLOR][/B] [B][COLOR=Royalblue]Each[/COLOR][/B] q [B][COLOR=Royalblue]In[/COLOR][/B] d.Keys
    i = i + [COLOR=crimson]1[/COLOR]: vb([COLOR=crimson]1[/COLOR], i) = q
[B][COLOR=Royalblue]Next[/COLOR][/B]

[B][COLOR=Royalblue]ReDim[/COLOR][/B] vc([COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(vb, [COLOR=crimson]2[/COLOR]))

[B][COLOR=Royalblue]For[/COLOR][/B] i = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]1[/COLOR])
    [B][COLOR=Royalblue]For[/COLOR][/B] j = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(va, [COLOR=crimson]2[/COLOR])
        [B][COLOR=Royalblue]For[/COLOR][/B] k = [COLOR=crimson]1[/COLOR] [B][COLOR=Royalblue]To[/COLOR][/B] UBound(vb, [COLOR=crimson]2[/COLOR])
            [B][COLOR=Royalblue]If[/COLOR][/B] InStr([COLOR=crimson]1[/COLOR], va(i, j), vb([COLOR=crimson]1[/COLOR], k), [COLOR=crimson]1[/COLOR]) [B][COLOR=Royalblue]Then[/COLOR][/B]
                [B][COLOR=Royalblue]If[/COLOR][/B] vc(i, k) = [COLOR=brown]""[/COLOR] [B][COLOR=Royalblue]Then[/COLOR][/B]
                    vc(i, k) = va(i, j)
                    [B][COLOR=Royalblue]Else[/COLOR][/B]
                    vc(i, k) = vc(i, k) & [COLOR=brown]", "[/COLOR] & va(i, j)
                [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
            [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
        [B][COLOR=Royalblue]Next[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
[B][COLOR=Royalblue]Next[/COLOR][/B]

va = Range(Cells([COLOR=crimson]1[/COLOR], [COLOR=brown]"A"[/COLOR]), Cells(a, [COLOR=brown]"A"[/COLOR]))

[B][COLOR=Royalblue]With[/COLOR][/B] Sheets([COLOR=brown]"Sheet2"[/COLOR])
.Range([COLOR=brown]"A1"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = va
.Range([COLOR=brown]"B1"[/COLOR]).Resize([COLOR=crimson]1[/COLOR], UBound(vb, [COLOR=crimson]2[/COLOR])) = vb
.Range([COLOR=brown]"B2"[/COLOR]).Resize(UBound(vc, [COLOR=crimson]1[/COLOR]), UBound(vc, [COLOR=crimson]2[/COLOR])) = vc
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]

[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

Please excuse me for being a beginner.. when i run this nothing happens... i dont see where the compare items are.. like looking for 'grp.app'
thanks.