excel - Count and Display Occurrences of Unique values in a row

Question

I have pallets that can hold up to 4 items. Sometimes the same item is one pallet. To print a shipping label for this pallet I need to list the items on the pallet and the quantity of that item. I have found found a few formulas and scripts to count unique instances of columns, but not data kept in the same row. Here is how the data comes to me:

I need to get it into this format:

I have done the above manually to show what I need a formula or script to do. I have been trying to figure this out all day so any help is appreciated. Thank you!

question from:https://stackoverflow.com/questions/65891627/count-and-display-occurrences-of-unique-values-in-a-row

Answer 1

You can also do this with Power Query, available in Excel 2010+

• Select a cell in the table
  • Data => Get & Transform => From Table/Range
• When the Power Query UI opens
  • Home => Advanced Editor and paste the M-Code into the window that opens
  • Change the Table name in Line 2 to reflect the real table name given your data
• Read the notes in the code, and examine the Applied Steps window to understand how it works

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item #1", type text}, {"Item #2", type text}, {"Item #3", type text}, {"Item #4", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Index"}, {
        {"Grouped", each _, type table [#"Item #1"=nullable text, #"Item #2"=nullable text, #"Item #3"=nullable text, #"Item #4"=nullable text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "uniqueItemList", each 
        let 
            //Get list of relevant column Names for grouping of each subTable
            colNames = Table.ColumnNames(Source),
            tbl1 = Table.SelectColumns([Grouped],colNames),

            //Transpose the table
            transp = Table.Transpose(tbl1),

            //Get a count of each unique Item
            grp = Table.Group(transp,"Column1",{"count", each List.Count([Column1])}),

            //create Lists of the items and their count and "Zip" them together
            col1 = Table.Column(grp,"Column1"),
            count = Table.Column(grp,"count"),
            zip = List.Zip({col1,count}),

            //create the Label string
            label = List.Accumulate(zip,"", (state, current)=> 
                if state = ""
                    then current{0} & ";" & Text.From(current{1}) 
                    else 
                        if List.NonNullCount(current) = 2 
                            then state & ";" & current{0} & ";" & Text.From(current{1})
                            else state)
        in 
            label),

    //split the label string into separate columns
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "uniqueItemList", Splitter.SplitTextByDelimiter
        (";", QuoteStyle.Csv), {"Slot #1", "Slot #1 Qty", "Slot #2", "Slot #2 Qty", "Slot #3", "Slot #3 Qty","Slot #4", "Slot #4 Qty"}),

    //Delete unneeded columns    
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Index", "Grouped"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Slot #1", type text}, {"Slot #1 Qty", Int64.Type}, {"Slot #2", type text}, {"Slot #2 Qty", Int64.Type}, {"Slot #3", type text}, {"Slot #3 Qty", Int64.Type}, {"Slot #4", type text}, {"Slot #4 Qty", Int64.Type}})
in
    #"Changed Type1"