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:
[TABLE="width: 476"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][TABLE="width: 2563"]
<colgroup><col><col span="36"></colgroup><tbody>[TR]
[TD]Item Code[/TD]
[TD]Jul-2016[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Aug-2016[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Sep-2016[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Oct-2016[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Nov-2016[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Dec-2016[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Jan-2017[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Feb-2017[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Mar-2017[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Apr-2017[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]May-2017[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Jun-2017[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]10010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]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.

[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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