Microsoft Excel Query - Formatting Columns

Question

I have got an excel sheet data that looks like this:

Order Id    Rate    Item    Quantity
XXX          4      Ball      100
XXX          4      Hockey    500
XXX          4      Hockey    500
AAA          3      Ball      200
AAA          3      Ball      300
AAA          6      Helmet    800
AAA          6      Helmet    100

I need to get the Quantity Subtotaled under the same orderId and Rate and the different Items under the same order to be separated by comma under Item. Basically the output should look like this :

Order Id    Rate      Item     Quantity
XXX          4   Ball, Hockey   1100
AAA          3       Ball       500
AAA          6      Helmet      900

Output screenshot in excel -

Answer 1

Sounds like a job for pivot table!

Select the range and go to Insert - Pivot Table

For row labels, you can use rate and order id

For column labels you can use item

And you can sum the values of qty

click for larger