Excel - Update Item Description Based on Accessories Ordered with It

Question

Looking for some advice on a business order hypothetical. As per the attached image, a person orders some mobile phones and some N number of accessories. I'm looking to update the phones description based on quantity and what accessories have been orders for each order ID.

Eacher Order ID and Phone S/N are unique, and only phones have a S/N. The number of accessories won't exceed the number of phones.

Order ID   Phone S/N    Description    Quantity Updated Description
O-001      P-001    Samsung S22    1        Samsung S22 + Plastic
O-001      P-002    Samsung S22    1        Samsung S22 + Glass
O-001           Plastic Protector  1    
O-001           Glass Protector    1    
O-002      P-003    Samsung S22    1        Samsung S22 + Plastic
O-002      P-004    Samsung S22    1        Samsung S22 + Plastic
O-002           Plastic Protector  2    
O-003      P-005    Samsung S22    1            Samsung S22
O-003      P-006    Samsung S22    1        Samsung S22 + Glass
O-003           Glass Protector    1    

Currently, I have working formulas for 0 or 1 type of accessory, but no clue how to proceed if there's 2 or more. Any help is greatly appreciated.

Answer 1

This could be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range or from within sheet
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

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

//Repeat rows per quantity
    #"Repeat Rows" = Table.AddColumn(Source,"Repeats", each Table.Repeat(Table.FromRecords({_}),[Quantity]),
    type table[Order ID=text, #"Phone S/N"=text, Description=text, Quantity=Int64.Type]),
    #"Removed Columns" = Table.RemoveColumns(#"Repeat Rows",Table.ColumnNames(Source)),
    #"Expanded Repeats" = Table.ExpandTableColumn(#"Removed Columns", "Repeats", 
        Table.ColumnNames(Source)),

//Group by Order ID, then process group separately
    #"Grouped Rows" = Table.Group(#"Expanded Repeats", {"Order ID"}, {
        {"Updated", (t)=>
            let
               
            //create updated description text strings
                phones =  Table.SelectRows(t, each [#"Phone S/N"] <> null),
                accessories = Table.SelectRows(t, each [#"Phone S/N"]=null),
                updated = List.Transform(List.Zip({phones[Description],accessories[Description]}),each Text.Combine(_," + ")),

            //Add a column of the Table with the updated description column added
                #"Add Column" = 
                    Table.FromColumns(
                        Table.ToColumns(t)
                        & {updated},{"Order ID", "Phone S/N", "Description","Quantity", "Updated Description"} )
            in 
                #"Add Column", type table [Order ID=nullable text, #"Phone S/N"=nullable text, 
                    Description=nullable text, Quantity=nullable number, Updated Description = nullable text]}}),

//Re-expand the table and remove the duplicated rows
    #"Expanded Updated" = Table.ExpandTableColumn(#"Grouped Rows", "Updated", 
        {"Phone S/N", "Description", "Quantity", "Updated Description"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Updated")
        
in
    #"Removed Duplicates"