Pivot Table

J

john5599

Board Regular
Joined
Mar 11, 2010
Messages
222
I have an excel sheet as below

Excel 2007
ABCDEFGHIJK
2DateCustomer NameNarrationAmountCash RecivedCheque RecivedType
305/12/2012Madan PuraBill No.156515000InvoiceBill NoBill NoInvoice
405/12/2012Jerry Pvt. LtdBill No.145610000InvoiceSales rSales returnSales Return
505/12/2012Jackson Pvt. LtdBill No.145825000InvoiceCash TrCash Transfer to Bank A/CCash Transfer
605/12/2012Jackson Pvt. LtdCash Received25000Cash ReceivedDiscounDiscount GivenDiscount
718/12/2012Jackson Pvt. LtdCheque Received1800Cheque ReceivedChequeCheque ReceivedCheque Received
805/12/2012Rupa & CompanyBill No.15607500InvoiceCash ReCash ReceivedCash Received
905/12/2012Vidhya Sagar Pvt. Ltd.Bill No.162015200InvoiceGoods RGoods ReturnSales Return
1005/12/2012Mahesh & SonsBill No.16223500InvoiceCash T/Cash T/F to BankCash Transfer
1105/12/2012Mruthula & Co.Bill No.17616500Invoice
1205/12/2012Markos Bros.Bill No.176210200Invoice
1305/12/2012Madan PuraBill No.176415300Invoice
1405/12/2012Jimmy TradersBill No.163010700Invoice
1505/12/2012Varun AssociatesBill No.17634500Invoice
1605/12/2013Varun AssociatesCash Received By5000Cash Received
1705/12/2012George Peter GroupsBill No.178010300Invoice
1805/12/2012Vikram AssociatesBill No.18631050Invoice
1915/05/2012Jerry Pvt. LtdBill No.18341000Invoice
2015/05/2012Rupa & CompanyBill No.1865750Invoice
2115/05/2012Mruthula & Co.Bill No.1890400Invoice
2216/05/2012Rupa & CompanyCash Transfer to Bank A/C7000Cash Transfer
2315/05/2012Varun AssociatesBill No.1925700Invoice
2415/05/2012Rupa & CompanyBill No.18968000Invoice
2518/05/2013Jackson Pvt. LtdBill No.97530000Invoice
2615/05/2012Mahesh & SonsBill No.19251400Invoice
2715/05/2012Jimmy TradersBill No.184712000Invoice
2815/05/2012Vidhya Sagar Pvt. Ltd.Bill No.19481020Invoice
2915/05/2012Vikram AssociatesBill No.1884500Invoice
3018/05/2012Markos Bros.Bill No.19271020Invoice
3118/05/2012Jackson Pvt. LtdBill No.20605220Invoice
3218/05/2012George Peter GroupsBill No.24354500Invoice
3318/05/2012Jimmy TradersBill No.19451800Invoice
3418/05/2012Madan PuraBill No.17908000Invoice
3520/05/2012Rupa & CompanyBill No.15361110Invoice
3620/05/2012Vikram AssociatesBill No.15251600Invoice
3720/05/2012Jerry Pvt. LtdBill No.15856660Invoice
3820/05/2012Varun AssociatesBill No.18903321Invoice
3920/05/2012Mahesh & SonsBill No.18643380Invoice
4025/05/2012George Peter GroupsBill No.1924839Invoice
4125/05/2012Vidhya Sagar Pvt. Ltd.Bill No.1938332Invoice
4212/06/2012Jackson Pvt. LtdSales return1800Sales Return
4325/07/2012Jackson Pvt. LtdCash Transfer3600Cash Transfer
4418/08/2012Jackson Pvt. LtdGoods Return360Sales Return
4518/05/2013Jackson Pvt. LtdDiscount Given350Discount
4620/05/2013Mahesh & SonsCash T/F to Bank1200Cash Transfer
4720/05/2013George Peter GroupsCheque Received12000Cheque Received
4810/06/2013George Peter GroupsGoods Return985Sales Return
Invoice-Cash-Cheque
Cell Formulas
RangeFormula
I3=LEFT(J3,7)
I4=LEFT(J4,7)
I5=LEFT(J5,7)
I6=LEFT(J6,7)
I7=LEFT(J7,7)
I8=LEFT(J8,7)
I9=LEFT(J9,7)
I10=LEFT(J10,7)
G3=VLOOKUP(LEFT(C3,7),$I$3:$K$10,3,FALSE)
G4=VLOOKUP(LEFT(C4,7),$I$3:$K$10,3,FALSE)
G5=VLOOKUP(LEFT(C5,7),$I$3:$K$10,3,FALSE)
G6=VLOOKUP(LEFT(C6,7),$I$3:$K$10,3,FALSE)
G7=VLOOKUP(LEFT(C7,7),$I$3:$K$10,3,FALSE)
G8=VLOOKUP(LEFT(C8,7),$I$3:$K$10,3,FALSE)
G9=VLOOKUP(LEFT(C9,7),$I$3:$K$10,3,FALSE)
G10=VLOOKUP(LEFT(C10,7),$I$3:$K$10,3,FALSE)
G11=VLOOKUP(LEFT(C11,7),$I$3:$K$10,3,FALSE)
G12=VLOOKUP(LEFT(C12,7),$I$3:$K$10,3,FALSE)
G13=VLOOKUP(LEFT(C13,7),$I$3:$K$10,3,FALSE)
G14=VLOOKUP(LEFT(C14,7),$I$3:$K$10,3,FALSE)
G15=VLOOKUP(LEFT(C15,7),$I$3:$K$10,3,FALSE)
G16=VLOOKUP(LEFT(C16,7),$I$3:$K$10,3,FALSE)
G17=VLOOKUP(LEFT(C17,7),$I$3:$K$10,3,FALSE)
G18=VLOOKUP(LEFT(C18,7),$I$3:$K$10,3,FALSE)
G19=VLOOKUP(LEFT(C19,7),$I$3:$K$10,3,FALSE)
G20=VLOOKUP(LEFT(C20,7),$I$3:$K$10,3,FALSE)
G21=VLOOKUP(LEFT(C21,7),$I$3:$K$10,3,FALSE)
G22=VLOOKUP(LEFT(C22,7),$I$3:$K$10,3,FALSE)
G23=VLOOKUP(LEFT(C23,7),$I$3:$K$10,3,FALSE)
G24=VLOOKUP(LEFT(C24,7),$I$3:$K$10,3,FALSE)
G25=VLOOKUP(LEFT(C25,7),$I$3:$K$10,3,FALSE)
G26=VLOOKUP(LEFT(C26,7),$I$3:$K$10,3,FALSE)
G27=VLOOKUP(LEFT(C27,7),$I$3:$K$10,3,FALSE)
G28=VLOOKUP(LEFT(C28,7),$I$3:$K$10,3,FALSE)
G29=VLOOKUP(LEFT(C29,7),$I$3:$K$10,3,FALSE)
G30=VLOOKUP(LEFT(C30,7),$I$3:$K$10,3,FALSE)
G31=VLOOKUP(LEFT(C31,7),$I$3:$K$10,3,FALSE)
G32=VLOOKUP(LEFT(C32,7),$I$3:$K$10,3,FALSE)
G33=VLOOKUP(LEFT(C33,7),$I$3:$K$10,3,FALSE)
G34=VLOOKUP(LEFT(C34,7),$I$3:$K$10,3,FALSE)
G35=VLOOKUP(LEFT(C35,7),$I$3:$K$10,3,FALSE)
G36=VLOOKUP(LEFT(C36,7),$I$3:$K$10,3,FALSE)
G37=VLOOKUP(LEFT(C37,7),$I$3:$K$10,3,FALSE)
G38=VLOOKUP(LEFT(C38,7),$I$3:$K$10,3,FALSE)
G39=VLOOKUP(LEFT(C39,7),$I$3:$K$10,3,FALSE)
G40=VLOOKUP(LEFT(C40,7),$I$3:$K$10,3,FALSE)
G41=VLOOKUP(LEFT(C41,7),$I$3:$K$10,3,FALSE)
G42=VLOOKUP(LEFT(C42,7),$I$3:$K$10,3,FALSE)
G43=VLOOKUP(LEFT(C43,7),$I$3:$K$10,3,FALSE)
G44=VLOOKUP(LEFT(C44,7),$I$3:$K$10,3,FALSE)
G45=VLOOKUP(LEFT(C45,7),$I$3:$K$10,3,FALSE)
G46=VLOOKUP(LEFT(C46,7),$I$3:$K$10,3,FALSE)
G47=VLOOKUP(LEFT(C47,7),$I$3:$K$10,3,FALSE)
G48=VLOOKUP(LEFT(C48,7),$I$3:$K$10,3,FALSE)

I have a Pivot Table like below

Excel 2007
ABCDEFGH
2
3Sum of Field1Column Labels
4Row LabelsCash ReceivedCash TransferCheque ReceivedDiscountInvoiceSales ReturnGrand Total
5George Peter Groups001200001563998528624
6Jackson Pvt. Ltd250003600180035060220216093130
7Jerry Pvt. Ltd000017660017660
8Jimmy Traders000024500024500
9Madan Pura000038300038300
10Mahesh & Sons0120000828009480
11Markos Bros.000011220011220
12Mruthula & Co.0000690006900
13Rupa & Company070000017360024360
14Varun Associates50000008521013521
15Vidhya Sagar Pvt. Ltd.000016552016552
16Vikram Associates0000315003150
17Grand Total3000011800138003502283023145287397
18
19
PivotTable



<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
I need to add one Balance column in pivot table

Balance should come
= Invoice(-)Cash Received,Cheque Received,Cash Transfer,Sales Return,Discount

How? Is it possible
 
Last edited:
Sort by date Sort by votes

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Ok, I see the problem. I hadn't noticed that you were just using one field "Type" in the Column Labels area instead of separate fields for each type.

You can use very similar steps to make a Calculated Item (not Field).

First do a simple test....

Click on the PivotTable on the Column Label "Cash Received"
In the Ribbon > Options tab > Fields, Items, & Sets > Calculated Item....
In the Name: TEST
Formula: =Invoice-'Cash Received'
Click Add > OK

If that works follow the same steps to build the Calculated Item:
Name: Balance
Formula: = Invoice-( 'Cash Received'+ 'Cheque Received'+ 'Cash Transfer'+ 'Sales Return'+ Discount)

You can paste the above formula, but typically it is better to pick the fields and items from the lists to ensure there are no spelling errors.

Sorry that I did not study your screen shot well enough initially to recognize that a Calculated Item was needed.
 
Upvote 0
JS411 said:
Ok, I see the problem. I hadn't noticed that you were just using one field "Type" in the Column Labels area instead of separate fields for each type.

You can use very similar steps to make a Calculated Item (not Field).

First do a simple test....

Click on the PivotTable on the Column Label "Cash Received"
In the Ribbon > Options tab > Fields, Items, & Sets > Calculated Item....
In the Name: TEST
Formula: =Invoice-'Cash Received'
Click Add > OK

If that works follow the same steps to build the Calculated Item:
Name: Balance
Formula: = Invoice-( 'Cash Received'+ 'Cheque Received'+ 'Cash Transfer'+ 'Sales Return'+ Discount)

You can paste the above formula, but typically it is better to pick the fields and items from the lists to ensure there are no spelling errors.

Sorry that I did not study your screen shot well enough initially to recognize that a Calculated Item was needed.
Click to expand...

Thank you so much sir. It is working now.
In my original Data there is some more field to add with invoice. I just do it like below.

I don't know the formula which I used in calculated item in a right manner. Even though I got the result.
Excel 2007
ABC
Calculated Field
Solve OrderFieldFormula
Field1=SUM(Amount,'Cash Recived','Cheque Recived')
Calculated Item
Solve OrderItemFormula
Balance=Invoice +'Chque Return' +'Cheque Paid' -'Cash Received' -'Cash Transfer' -'Cheque Received' -Discount -'Sales Return'

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet6​



Now I got the result. Thanks a lot.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
172
SBExcel123
S
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
588
awoohaw
awoohaw
M
  • Question Question
Bifurcate total expenses on the basis of calendar months other than AD
Replies
2
Views
486
MandeepBajimaya
M
G
  • Question Question
Find Values that Match Criteria from Different Sheet and Copy over to Another
Replies
0
Views
319
glaccounting
G
94mustang
  • Solved
Conditional Formatting on Quarterly Dates
Replies
7
Views
2K
Micron
M

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top