Search code examples
excelcategorization

Categorizing items with names on Excel

I am trying to rearrange matrix elements to categorize items matching with names. For example:

The original matrix:

Name, item1, item2, item3 ... 

A,    apple, banana, cherry

B,    milk, apple, cherry

C,    milk, chocolate, apple

and then I want to convert this matrix into:

items, names

apple, A B C 

banana, A

cherry, A B

milk, B C

chocolate, C

Currently, I am just copying and pasting on excel, and it is very inefficient. Is there any way to categorize the items?

Solution

  • Please refer to this article to find out how to use Power Query on your version of Excel. It is available in Excel 2010 Professional Plus and later versions. My demonstration is using Excel 2016.

    Steps are:

    1. Add your source data to power query editor;
    2. Highlight all Item columns, use Unpivot Columns function under Transform tab to unpivot these columns, then sort your table by Value then by Name column;
    3. Use Group By function under the Transform tab to group the Name column by Value column as set out below:

    Group By

    1. Go back to the last step in the APPLIED STEPS section on the right hand side, go to the formula bar to replace this part of the formula List.Sum([Name]) with Text.Combine([Name]," ");
    2. Close & Load the result to a new worksheet (by default).

    If all your names are consistent without any hidden spaces in front or at the end of the string, you should have the following as a result:

    Result

    Here is my Power Query M code for your reference:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {" item1", type text}, {" item2", type text}, {" item3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Value", Order.Ascending}, {"Name", Order.Ascending}}),
    #"Trimmed Text" = Table.TransformColumns(#"Sorted Rows",{{"Value", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Value", Text.Clean, type text}}),
    #"Grouped Rows" = Table.Group(#"Cleaned Text", {"Value"}, {{"names", each Text.Combine([Name]," "), type text}})
in
    #"Grouped Rows"

    Let me know if you have any questions. Cheers :)