# 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

• 25.9 KB Views: 4

#### 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

• 29.2 KB Views: 5

#### 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

• 37.7 KB Views: 7
Last edited:
• Shabbo

#### 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

• 142.7 KB Views: 1

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