• 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.

Data Summary

Shabbo

Member
Dear Sir,

Column B is bill number Column A is the customer name.
Can I get customer name in front of bill number as shown in sheet 3

Also can we apply pivot table or get summary of customer wise closing balance Debit minus credit.
Example Customer name RAVESHIA ltd closing balance is Rs 1194970/-
 

Attachments

bosco_yip

Excel Ninja
1] Customer name in respect of Bill number

In I4, formula :

=LOOKUP("zzz",Sheet2!$A$1:INDEX(Sheet2!$A$1:$A$1000,INDEX(MATCH(2,1/(Sheet2!$B$1:$B$1000=B4)),0)))

2] Summary of customer wise closing balance (Formula solution)

In "Customer name" B10, copied down :

=IFERROR(INDEX(Sheet2!$A:$A,AGGREGATE(15,6,ROW($A$2:$A$1000)/(Sheet2!$A$2:$A$1000<>"Group Total")/ISTEXT(Sheet2!$A$2:$A$1000),ROW(A1))),"")

In "Closing balance" C10, copied down :

=IF(B10="","",OFFSET(INDEX(Sheet2!$H$1:$H$1000,MATCH(B10,Sheet2!$A$1:$A$1000,0)),MATCH("Group Total",INDEX(Sheet2!$A$1:$A$1000,MATCH(B10,Sheet2!$A$1:$A$1000,0)):Sheet2!$A$1000,0)-2,))

Regards
Bosco
 

Attachments

p45cal

Well-Known Member
Shabbo, here's a snippet of code that you can run in the file you attached (see comments in the code):
Code:
Sub blah()
'copy sheet2 and rearrange to make suitable for a pivot table data source
Sheets("Sheet2").Copy After:=Sheets(Sheets.Count)
Set NewSht = ActiveSheet
With NewSht
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Columns("A:A").Insert
  For Each cll In .Range("B2:B" & lr).Cells
    If cll.Font.Italic Then cll.Offset(, -1).Value = cll.Value
  Next cll
  .Range("A2:A" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  .Range("A1").Value = "Co. Name"
  .Range("A1:A" & lr).Value = Range("A1:A" & lr).Value
  .Range("C1:C" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
'end of data rearrangment

'create a pivot cache for pivot table(s)
Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewSht.UsedRange.Resize(, 9)) ', Version:=6)

'create a smaller pivot table:
Set PT = pc.CreatePivotTable(TableDestination:=Sheets("Sheet3").Range("A8"))
With PT
  '.ColumnGrand = False
  .RowGrand = False
  .RowAxisLayout xlTabularRow
  .RepeatAllLabels xlRepeatLabels
  .CalculatedFields.Add "The Balance", "=Debit - Credit", True
  .PivotFields("The Balance").Orientation = xlDataField
  With .PivotFields("Co. Name")
    .Orientation = xlRowField
    .Position = 1
  End With
  With .PivotFields("Sum of The Balance")
    .NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
  End With
End With

'create a larger pivot table:
Set PT2 = pc.CreatePivotTable(TableDestination:=Sheets("Sheet3").Range("K3"))
With PT2
  .RowAxisLayout xlTabularRow
  .ColumnGrand = False
  .RowGrand = False
  .ShowDrillIndicators = False
  With .PivotFields("Co. Name")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields("VCHDate")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields(" Ref No.")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields("Cheque No.")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields("Particulars")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields("VCHType")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  .AddDataField .PivotFields("Debit"), "Sum of Debit", xlSum
  .AddDataField .PivotFields("Credit"), "Sum of Credit", xlSum
  .AddDataField .PivotFields("Balance"), "Sum of Balance", xlSum
  .PivotFields("Sum of Debit").NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
  .PivotFields("Sum of Credit").NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
  .PivotFields("Sum of Balance").NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
End With
End Sub
 

Attachments

Last edited:

Shabbo

Member
Shabbo, here's a snippet of code that you can run in the file you attached (see comments in the code):
Code:
Sub blah()
'copy sheet2 and rearrange to make suitable for a pivot table data source
Sheets("Sheet2").Copy After:=Sheets(Sheets.Count)
Set NewSht = ActiveSheet
With NewSht
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Columns("A:A").Insert
  For Each cll In .Range("B2:B" & lr).Cells
    If cll.Font.Italic Then cll.Offset(, -1).Value = cll.Value
  Next cll
  .Range("A2:A" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  .Range("A1").Value = "Co. Name"
  .Range("A1:A" & lr).Value = Range("A1:A" & lr).Value
  .Range("C1:C" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
'end of data rearrangment

'create a pivot cache for pivot table(s)
Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewSht.UsedRange.Resize(, 9)) ', Version:=6)

'create a smaller pivot table:
Set PT = pc.CreatePivotTable(TableDestination:=Sheets("Sheet3").Range("A8"))
With PT
  '.ColumnGrand = False
  .RowGrand = False
  .RowAxisLayout xlTabularRow
  .RepeatAllLabels xlRepeatLabels
  .CalculatedFields.Add "The Balance", "=Debit - Credit", True
  .PivotFields("The Balance").Orientation = xlDataField
  With .PivotFields("Co. Name")
    .Orientation = xlRowField
    .Position = 1
  End With
  With .PivotFields("Sum of The Balance")
    .NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
  End With
End With

'create a larger pivot table:
Set PT2 = pc.CreatePivotTable(TableDestination:=Sheets("Sheet3").Range("K3"))
With PT2
  .RowAxisLayout xlTabularRow
  .ColumnGrand = False
  .RowGrand = False
  .ShowDrillIndicators = False
  With .PivotFields("Co. Name")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields("VCHDate")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields(" Ref No.")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields("Cheque No.")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields("Particulars")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  With .PivotFields("VCHType")
    .Orientation = xlRowField
    .Subtotals(1) = True
    .Subtotals(1) = False
  End With
  .AddDataField .PivotFields("Debit"), "Sum of Debit", xlSum
  .AddDataField .PivotFields("Credit"), "Sum of Credit", xlSum
  .AddDataField .PivotFields("Balance"), "Sum of Balance", xlSum
  .PivotFields("Sum of Debit").NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
  .PivotFields("Sum of Credit").NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
  .PivotFields("Sum of Balance").NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
End With
End Sub
Dear Sir,
Can you please check the error because pivot is not generated.
 

Attachments

p45cal

Well-Known Member
I've just checked by downloading the attachment I posted in msg#3, and running the macro blah. A new sheet was added with data for the pivot tables and 2 pivot tables were added to Sheet3.
1. Are you trying it in the workbook I posted?
2. Have you looked at Sheet3?
3. Was a new sheet with data created? (I'm pretty sure it is)
4. What was the error message?
5. Have you run it more than once without first deleting the 2 pivot tables the macro created the last time it was run?
 

Shabbo

Member
I've just checked by downloading the attachment I posted in msg#3, and running the macro blah. A new sheet was added with data for the pivot tables and 2 pivot tables were added to Sheet3.
1. Are you trying it in the workbook I posted?
2. Have you looked at Sheet3?
3. Was a new sheet with data created? (I'm pretty sure it is)
4. What was the error message?
5. Have you run it more than once without first deleting the 2 pivot tables the macro created the last time it was run?
Dear Sir,
I generated my new report and pasted data in the file you sent to me.
 

p45cal

Well-Known Member
Let's try again.
1. Did you run the blah macro in the unaltered workbook I attached in msg#3?
2. If so, did it produce a new sheet of data and 2 new pivots on Sheet3?
The above will establish whether there are version problems.
3. What is the error message you get when the error occurs?

Please attach the workbook which has your new report and throws an error.
 
Top