INDEX/MATCH values and SUM for duplicate values in adjacent cell

[sabrez]

Hi Everyone,


My first post here, so please excuse any shortcomings


Below is my table:


Item Date Quantity Amount
10010 Jul-2016 23 $32.91
10010 Jul-2016 10 $14.31
10010 Jul-2016 -10 -$14.30
10010 Jul-2016 10 $15.90
10010 Jul-2016 10 $15.90
10010 Aug-2016 3 $4.77
10010 Aug-2016 17 $27.03
10010 Sep-2016 2 $3.18
10010 Oct-2016 4 $6.36
10010 Dec-2016 1 $1.59
10010 Dec-2016 15 $23.85
10013 Jul-2016 10 $11.25
10013 Jul-2016 10 $11.25
10013 Jul-2016 -10 -$11.25
10013 Jul-2016 4 $5.00
10013 Aug-2016 200 $250.00
10013 Sep-2016 2 $2.50
10013 Sep-2016 2 $2.50
10013 Oct-2016 200 $250.00
10013 Oct-2016 200 $250.00
10013 Dec-2016 200 $250.00
10013 Jan-2017 2 $2.50
10013 Feb-2017 1 $1.25
10013 Mar-2017 1 $1.25
10013 Mar-2017 6 $6.75
10014 Jul-2016 10
10014 Jul-2016 90 $33.21
10014 Oct-2016 20 $8.20
10014 Oct-2016 50 $20.50


Now I want to consolidate the above table onto another sheet as follows:

Item Code Jul-2016 Qty Amount Aug-2016 Qty Amount Sep-2016 Qty Amount Oct-2016 Qty Amount Nov-2016 Qty Amount Dec-2016 Qty Amount Jan-2017 Qty Amount Feb-2017 Qty Amount Mar-2017 Qty Amount Apr-2017 Qty Amount May-2017 Qty Amount Jun-2017 Qty Amount 10010 10011 10013 10014 <colgroup><col><col span="36"></colgroup><tbody> </tbody>
For the months against each item code that appears more than once, it should sum the values under Qty and Amount. Any simple way of doing this? Any help would be much appreciated.

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[mrshl9898]

You can just use a pivot table.

Highlight the data and click > Insert > Pivot Table.

Add "Item" to Rows, "Date" to Columns and "Quantity" and "Amount" to values

 

[sabrez]

Thanks...that helped