• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for Total by each Customer with its Narration

J

jack999

Member
I need a macro which can Total each customer transaction with its Narration. How It possible?

Total of Customer by Customer with its Narration like below

Customer Name Bills Cash Received Cheque Received Sales Return ....
Each narrations total should come one by one column.

Sample file attached. Output Sheet is my sample requirement sheet

Is this complicated do with macro? Can someone help me?
 

Attachments

  • AC-Statement11111.xlsx
    23.7 KB · Views: 7
I think it would be faster to just create a PivotTable, as your data is already in a pretty good layout.
 

Attachments

  • Example AC-Statement11111.xlsx
    29.7 KB · Views: 7
Luke M said:
I think it would be faster to just create a PivotTable, as your data is already in a pretty good layout.
Click to expand...
Sir I don't have enough knowledge about Pivot Table so that I suggest for macro. Can you explain how you create this table.
 
Luke M said:
Hi Jack,
PivotTable are pretty amazing once you start using them. A couple of into articles:
http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
http://www.contextures.com/CreatePivotTable.html

It's created by selected your table/data, and then going to Insert - Tables - PivotTables. After that, you can just drag the fields (column headings) to different parts of the table, and XL will do the calculations for you. :)
Click to expand...
Sir how to add sum value Sum of Field1 in Pivot table
 
After reading the article Narayan posted, you should be able to discover that Field1 is a calculated Field I made that just adds 3 of the fields in your table. So I could have them all together. Alternatively, you can add a field to the raw data table that does the sum, and just have that. Six one way, half a dozen the other.
 
Luke M said:
After reading the article Narayan posted, you should be able to discover that Field1 is a calculated Field I made that just adds 3 of the fields in your table. So I could have them all together. Alternatively, you can add a field to the raw data table that does the sum, and just have that. Six one way, half a dozen the other.
Click to expand...
Sir, Can we use add then Subtract formula in Pivot table as need a balance column in that table.

Eg.
Add

Invoice(H5)
Cheque Paid(D5)
Cheque Return(F5)

then need to subtract

Cash Received(B5)
Discount(G5)
Sales Return(I5)
Cheque Received(E5)
Cash Transfer(C5)
to get balance column in Pivot Table, what will be the command
 
You'd probably be able to do that easiest by creating a new column in you data that does the math, ie
=SUM(H5,D5,F5,-B5,-G5,-I5,-E5,-C5)
And then add that new column to the PivotTable.
 
Hi jack999,

Can you please check the attached.. if its working..

Code: 
Sub TotCustNarra()
    Sheets("Output").Cells.Clear
    With Sheets("database")
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A2:A" & lr).Copy Sheets("Output").Range("A2")
    End With
   
    With Sheets("Output")
        .Range("A1:I1") = Array("Party Name", "Bill Amount", "Cash Received", "Cheque Received", "Sales Return", "Cash Transfer", "Goods Return", "Discount Given", "Cash T/F to Bank")
        .Range("B2:B" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!D:D,'Invoice-Cash-Cheque'!$B:$B,Output!$A2)"
        .Range("C2:I" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!$E:$E,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!E$1)"
        .Range("F2:F" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!F:F,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!D$1)"
        .Activate
    End With
    ActiveWindow.DisplayZeros = False
End Sub
 

Attachments

  • total-each-customer-narration # 12778.xlsm
    34.2 KB · Views: 4
Debraj said:
Hi jack999,

Can you please check the attached.. if its working..

Code: 
Sub TotCustNarra()
    Sheets("Output").Cells.Clear
    With Sheets("database")
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A2:A" & lr).Copy Sheets("Output").Range("A2")
    End With
 
    With Sheets("Output")
        .Range("A1:I1") = Array("Party Name", "Bill Amount", "Cash Received", "Cheque Received", "Sales Return", "Cash Transfer", "Goods Return", "Discount Given", "Cash T/F to Bank")
        .Range("B2:B" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!D:D,'Invoice-Cash-Cheque'!$B:$B,Output!$A2)"
        .Range("C2:I" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!$E:$E,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!E$1)"
        .Range("F2:F" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!F:F,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!D$1)"
        .Activate
    End With
    ActiveWindow.DisplayZeros = False
End Sub
Click to expand...


Thank you so much Sir, It is working good.
 
Luke M said:
You'd probably be able to do that easiest by creating a new column in you data that does the math, ie
=SUM(H5,D5,F5,-B5,-G5,-I5,-E5,-C5)
And then add that new column to the PivotTable.
Click to expand...

Sir, in PivotTable itself can we use this formula?. Because in my Invoice-Cash-Cheque sheet (as in the uploaded sheet) don't have similar data in particular Columns. Sir my question is can we add apply the formula in PivotTable by add another value filed. I mean insert another calculated field for this?

Regards.
 
jack999 said:
Sir, in PivotTable itself can we use this formula?. Because in my Invoice-Cash-Cheque sheet (as in the uploaded sheet) don't have similar data in particular Columns. Sir my question is can we add apply the formula in PivotTable by add another value filed. I mean insert another calculated field for this?

Regards.
Click to expand...

Yes, you can. Go to PivotTable Tools - Tools - Formulas - Calculated Field. There you can setup the equation to match your requirements and add the field to the PivotTable.
 
Debraj said:
Hi jack999,

Can you please check the attached.. if its working..

Code: 
Sub TotCustNarra()
    Sheets("Output").Cells.Clear
    With Sheets("database")
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A2:A" & lr).Copy Sheets("Output").Range("A2")
    End With
 
    With Sheets("Output")
        .Range("A1:I1") = Array("Party Name", "Bill Amount", "Cash Received", "Cheque Received", "Sales Return", "Cash Transfer", "Goods Return", "Discount Given", "Cash T/F to Bank")
        .Range("B2:B" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!D:D,'Invoice-Cash-Cheque'!$B:$B,Output!$A2)"
        .Range("C2:I" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!$E:$E,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!E$1)"
        .Range("F2:F" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!F:F,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!D$1)"
        .Activate
    End With
    ActiveWindow.DisplayZeros = False
End Sub
Click to expand...

Sir, if you have little time please following things.There is some errors in the macro. Eg. Jackson Pvt. Ltd there is sales return 1800 not showing in output sheet & discount given also not showing in output sheet.
calculation should need to make Col. B with Col.G
Sir please check Pivot Table in the attached file in the Example AC file
 

Attachments

  • Example AC-Statement5555.xlsm
    27.4 KB · Views: 2
  • total-each-customer-narration # 12778.xlsm
    30.7 KB · Views: 8
Hi Jack..
Its my fault.. at the last moment while submitting previous macro.. I forgot to change E$1 to C$1
Please check the updated one..

Code: 
Sub TotCustNarra()
    Sheets("Output").Cells.Clear
    With Sheets("database")
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A2:A" & lr).Copy Sheets("Output").Range("A2")
    End With
    
    With Sheets("Output")
        .Range("A1:I1") = Array("Party Name", "Bill Amount", "Cash Received", "Cheque Received", "Sales Return", "Cash Transfer", "Goods Return", "Discount Given", "Cash T/F to Bank")
        .Range("B2:B" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!D:D,'Invoice-Cash-Cheque'!$B:$B,Output!$A2)"
        .Range("C2:I" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!$E:$E,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!C$1)"
        .Range("D2:D" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!F:F,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!D$1)"
        .Activate
    End With
    ActiveWindow.DisplayZeros = False
End Sub
 
Debraj said:
Hi Jack..
Its my fault.. at the last moment while submitting previous macro.. I forgot to change E$1 to C$1
Please check the updated one..

Code: 
Sub TotCustNarra()
    Sheets("Output").Cells.Clear
    With Sheets("database")
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A2:A" & lr).Copy Sheets("Output").Range("A2")
    End With
   
    With Sheets("Output")
        .Range("A1:I1") = Array("Party Name", "Bill Amount", "Cash Received", "Cheque Received", "Sales Return", "Cash Transfer", "Goods Return", "Discount Given", "Cash T/F to Bank")
        .Range("B2:B" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!D:D,'Invoice-Cash-Cheque'!$B:$B,Output!$A2)"
        .Range("C2:I" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!$E:$E,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!C$1)"
        .Range("D2:D" & lr).Formula = "=SUMIFS('Invoice-Cash-Cheque'!F:F,'Invoice-Cash-Cheque'!$B:$B,Output!$A2,'Invoice-Cash-Cheque'!$C:$C,Output!D$1)"
        .Activate
    End With
    ActiveWindow.DisplayZeros = False
End Sub
Click to expand...

Thanks Sir, Now its ok.

If I want to add one more column in output file "Cheque Paid" then how to add code in the macro
 
You must log in or register to reply here.
Back
Top