Data consolidation with permanent cell and IF statement

[dmelnik]

Hello,
im having trouble with coming up with a formula for the following case:
We have sheet with data about different products:

Product	Quantity	Location
Product 1	5	1-1-1
Product 1	10	1-1-1
Product 1	5	1-1-2
Product 2	10	1-1-2
Product 3	5	1-1-2
Product 3	10	1-1-3

Our goal is to consolidate the table by Product with suming up the quantities, but keeping the Location information similar to 1-1-1 / 1-1-1 / 1-1-2. So after the require manipulations the table should look like this:

Product	Quantity	Location
Product 1	20	1-1-1 / 1-1-1 / 1-1-2
Product 2	10	1-1-2
Product 3	15	1-1-2 / 1-1-3

 

[sandy666]

you can try with Power Query aka Get&Transform

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Quantity", Int64.Type}, {"Location", type text}}),
    Group = Table.Group(Type, {"Product"}, {{"Quantity", each List.Sum([Quantity]), type number}, {"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Location", each [Count][Location]),
    Extract = Table.TransformColumns(List, {"Location", each Text.Combine(List.Transform(_, Text.From), "/"), type text})
in
    Extract

-------------------
more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[dmelnik]

Perfect! Thank you very much!

 

[sandy666]

You are welcome
Have a nice day