Thanks for feeding back and solution for the query you have asked for is
given below.
For Excel 2003
Select the D Column and go to Data>Pivot Table and Pivot Char
Report>Next>Next>Layout
In right of that popup window you can see a Field that is Product Code, Now
click and hold the mouse and drag it to Row. In the same manner do it once
again and take it in Data field also. Now both in Row and Data you can see
the field Product Code. Give ok then click Finish.
Now a new sheet is added and you will see the Product Codes without any
duplication. Just select the product codes and do copy. Now insert a new
sheet and place the cursor in A2 cell and do right click>PASTE SPECIAL>Values.
Now in A1 type Product Code
And in B1 type Issue
And in C1 type Received
And in D1 type Balance
In B2 paste this formula
=SUMIF(Sheet1!$D:$D,$A2,Sheet1!$F:$F)
In C2 paste this formula
=SUMIF(Sheet1!$D:$D,$A2,Sheet1!$G:$G)
In D2 paste this formula
=SUMIF(Sheet1!$D:$D,$A2,Sheet1!$G:$G)-SUMIF(Sheet1!$D:$D,$A2,Sheet1!$F:$F)
Or
=C2-B2
After applying the formula’s in the above cells now copy B2, C2 & D2 cells
and paste it to the remaining cells.
Note that your master data should be in Sheet1 worksheet, if it is not in
sheet1 worksheet then change the sheet name (Sheet1) in the above formula.
For any further clarifications please let us know.
If this post helps, Click Yes!