Summarize data

D

deba2020

New Member
Joined
Jan 8, 2020
Messages
26
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
I have two sheets in my workbook
Data in Sheet("Details") in my workbook look like this
Document NumberDocument DateDocument CCYDocument AmtOther DeductionAmount DeductableNet AmountShipment DateFinancing Tenor Start DateFinancing TenorMaturity DateSupplier IdSupplier Name
MUMV0362622-05-2023INR4,60,200.004,60,200.0022-05-202329-05-20236028-07-20231862VIMAL INTERTRADE PVT LTD
MUMV03626-TDS-CM-126709422-05-2023INR-390.00-390.0022-05-202329-05-20236028-07-20231862VIMAL INTERTRADE PVT LTD
PC/0129/23-2419-05-2023INR1,86,018.981,86,018.9819-05-202329-05-20236028-07-202318852PAPER CARTONS
NH/54/23-2416-05-2023INR27,71,112.0027,71,112.0016-05-202329-05-20236028-07-20231860NEPTUNE HYDROCARBONS MFG (PVT) LTD
NH/54/23-24-TDS-CM-126704416-05-2023INR-2,326.00-2,326.0016-05-202329-05-20236028-07-20231860NEPTUNE HYDROCARBONS MFG (PVT) LTD
166785129820-05-2023INR3,19,725.003,19,725.0020-05-202329-05-20236028-07-2023171520 Microns Limited
1667851298-TDS-CM-126709020-05-2023INR-305.00-305.0020-05-202329-05-20236028-07-2023171520 Microns Limited
PC/0129/23-2419-05-2023INR1,48,276.441,48,276.4419-05-202329-05-20236028-07-202318852PAPER CARTONS
NH/46/23-2409-05-2023INR17,90,461.2017,90,461.2009-05-202329-05-20236028-07-20231860NEPTUNE HYDROCARBONS MFG (PVT) LTD
NH/46/23-24-TDS-CM-126034909-05-2023INR-1,517.00-1,517.0009-05-202329-05-20236028-07-20231860NEPTUNE HYDROCARBONS MFG (PVT) LTD
MUMV1588921-02-2023INR46,020.0046,020.0021-02-202329-05-20236028-07-20231862VIMAL INTERTRADE PVT LTD


and I want to prepare summary of it in Sheet ("Summary") and it look like this
Supplier NameSupplier IDFinancing TenorDocument Amount (Rs.)Other Deduction + Amount Deductible (Rs.)Net Amount Payable (Rs.)Maturity Date
VIMAL INTERTRADE PVT LTD1862605,05,830.005,05,830.0028-Jul-23
PAPER CARTONS18852603,34,295.423,34,295.4228-Jul-23
NEPTUNE HYDROCARBONS MFG (PVT) LTD18606045,57,730.2045,57,730.2028-Jul-23
20 Microns Limited1715603,19,420.003,19,420.0028-Jul-23
Total57,17,275.620.0057,17,275.62


How to summarize the data using vba code or using scripting dictionary, Please help.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Hi
Try
Might need some cells formatting and the headers as well
VBA Code: 
Sub test()
    Dim a, w
    Dim i&
    Dim dst As Worksheet
    a = Sheets("sheet2").Cells(1).CurrentRegion '<< change sheet name to ypu source
    Set dst = Sheets("Sheet1") '<< change sheet name to ypu Destination sheet
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If Not .Exists(a(i, 13)) Then
                .Add a(i, 13), Array(a(i, 12), a(i, 10), a(i, 4), a(i, 5), a(i, 7), a(i, 11))
                w = .Item(a(i, 13)): w(3) = w(3) + a(i, 6): .Item(a(i, 13)) = w
            Else
                w = .Item(a(i, 13))
                w(2) = w(2) + a(i, 4): w(4) = w(4) + a(i, 7): w(3) = w(3) + a(i, 6) + a(i, 5)
                .Item(a(i, 13)) = w
            End If
        Next
        dst.Cells(2, 1).Resize(.Count) = Application.Transpose(.keys)
        dst.Cells(2, 2).Resize(.Count, 6) = Application.Index(.items, 0, 0)
        dst.Cells(2, 1).Offset(.Count) = "Total"
        dst.Cells(.Count + 2, 4) = WorksheetFunction.Sum(Application.Index(.items, 0, 3))
        dst.Cells(.Count + 2, 5) = WorksheetFunction.Sum(Application.Index(.items, 0, 4))
        dst.Cells(.Count + 2, 6) = WorksheetFunction.Sum(Application.Index(.items, 0, 5))
    End With
End Sub
 
Upvote 1
Hi again
This a revised
VBA Code: 
Sub test()
    Dim a, w
    Dim i&
    Dim dst As Worksheet
    Dim F: Set F = WorksheetFunction
    a = Sheets("Details").Cells(1).CurrentRegion
    Set dst = Sheets("Summary")
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If Not .Exists(a(i, 13)) Then
                .Add a(i, 13), Array(a(i, 13), a(i, 12), a(i, 10), a(i, 4), a(i, 5), a(i, 7), a(i, 11))
                w = .Item(a(i, 13)): w(4) = w(4) + a(i, 6): .Item(a(i, 13)) = w
            Else
                w = .Item(a(i, 13))
                w(3) = w(3) + a(i, 4): w(4) = w(4) + a(i, 6) + a(i, 5): w(5) = w(5) + a(i, 7)
                .Item(a(i, 13)) = w
            End If
        Next
        i = .Count: a = Application.Index(.items, 0, 0)
    End With
    With dst.Cells(2, 1).Resize(UBound(a), UBound(a, 2))
        .Resize(1).Offset(-1) = Array("Supplier Name", "Supplier ID", "Financing Tenor", "Document Amount (Rs.)", "Other Deduction + Amount Deductible (Rs.)", "Net Amount Payable (Rs.)", "Maturity Date")
        .Resize(i + 1).Borders.LineStyle = xlContinuous
        .Offset(i).Resize(1).Font.Bold = True
        .Value = a
        .EntireColumn.AutoFit
        .Columns("B:C").HorizontalAlignment = xlCenter
        .Columns("G").HorizontalAlignment = xlCenter
        With .Resize(1, 1)
            .Offset(i) = "Total"
            .Offset(i, 3) = F.Sum(Application.Index(a, 0, 4))
            .Offset(i, 4) = F.Sum(Application.Index(a, 0, 5))
            .Offset(i, 5) = F.Sum(Application.Index(a, 0, 6))
            .Offset(, 3).Resize(i, 3).NumberFormat = "#,##0.00"
             .Offset(, 6).Resize(i).NumberFormat = "dd,mmm,yyyy"
        End With: End With
End Sub
 
Upvote 0
mohadin said:
Hi again
This a revised
VBA Code: 
Sub test()
    Dim a, w
    Dim i&
    Dim dst As Worksheet
    Dim F: Set F = WorksheetFunction
    a = Sheets("Details").Cells(1).CurrentRegion
    Set dst = Sheets("Summary")
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If Not .Exists(a(i, 13)) Then
                .Add a(i, 13), Array(a(i, 13), a(i, 12), a(i, 10), a(i, 4), a(i, 5), a(i, 7), a(i, 11))
                w = .Item(a(i, 13)): w(4) = w(4) + a(i, 6): .Item(a(i, 13)) = w
            Else
                w = .Item(a(i, 13))
                w(3) = w(3) + a(i, 4): w(4) = w(4) + a(i, 6) + a(i, 5): w(5) = w(5) + a(i, 7)
                .Item(a(i, 13)) = w
            End If
        Next
        i = .Count: a = Application.Index(.items, 0, 0)
    End With
    With dst.Cells(2, 1).Resize(UBound(a), UBound(a, 2))
        .Resize(1).Offset(-1) = Array("Supplier Name", "Supplier ID", "Financing Tenor", "Document Amount (Rs.)", "Other Deduction + Amount Deductible (Rs.)", "Net Amount Payable (Rs.)", "Maturity Date")
        .Resize(i + 1).Borders.LineStyle = xlContinuous
        .Offset(i).Resize(1).Font.Bold = True
        .Value = a
        .EntireColumn.AutoFit
        .Columns("B:C").HorizontalAlignment = xlCenter
        .Columns("G").HorizontalAlignment = xlCenter
        With .Resize(1, 1)
            .Offset(i) = "Total"
            .Offset(i, 3) = F.Sum(Application.Index(a, 0, 4))
            .Offset(i, 4) = F.Sum(Application.Index(a, 0, 5))
            .Offset(i, 5) = F.Sum(Application.Index(a, 0, 6))
            .Offset(, 3).Resize(i, 3).NumberFormat = "#,##0.00"
             .Offset(, 6).Resize(i).NumberFormat = "dd,mmm,yyyy"
        End With: End With
End Sub
Click to expand...
Hi,
Your both the solutions are working perfectly.
Although it is an immense help but is this possible:
The summary sheet is like a letter to the bank which contains some text and this table, can we do something so that the table can be adjusted dynamically.
One thing is fixed that the table will start from Range("A45")
Please find the attached format

RBL HUNDI RTGS LETTER 30.05.2023-JEJURI Location.xlsx
ABCDEFGHIJ
2231-May-23
23
24ABC BANK
25XXXXXX,
26XXXXXXX, 342432
27
28Kind Attn : Mr. XXXX
29
30Dear Sirs,
31
32Sub: Purchase Invoice Discounting
33
34We would request to you to discount all invoices set out in the Appendix hereto, and remit the discounted proceeds to the beneficiaries
35by way of credit to their accounts, details of which are given below.
36
37Interest may be recovered upfront from our account maintained with your bank.
38
39
40Discounting, as requested, is against purchases made by us from various vendors, and are due to be paid to them on dates as mentioned
41 in the “Statement of Invoices” set out in the Appendix hereto.
42
43Proceeds, net of discounting charges, are to be remitted to :
44
45Beneficiary (Vendor)ERP IDTenorTotal Invoice Amount (Rs.)Amount Deductible (Rs.)Net Amount Payable (Rs.)Due DateBeneficiary Bank Name & AddressAccount NumberIFSC Code
46VIMAL INTERTRADE PVT LTD1862605,05,830.000.005,05,830.0028-07-2023
47
48Total57,17,275.620.0057,17,275.62
49
50We irrevocably and unconditionally authorize you to debit our current account number XXXXXXXXXXXX maintained with your bank
51 on various due dates towards recovery of the discounted proceeds along with discounting charges at the agreed rate calculated
52on the discounted proceeds for the tenor of discounting.
53
54We also confirm that:
55• No other form of finance has been availed or will be availed against the underlying invoices
56• Goods / Services mentioned in the underlying invoices have been received by us in good order.
57
58Yours faithfully,
Summary
 
Upvote 0
Hi
To be honest with you I couldn't understand your request
More elaborate please!!
Thsanks
 
Upvote 0
An alternative to VBA is with Power Query. Here is the Mcode for that
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document Number", type any}, {"Document Date", type date}, {"Document CCY", type text}, {"Document Amt", type number}, {"Other Deduction", type any}, {"Amount Deductable", type any}, {"Net Amount", type number}, {"Shipment Date", type date}, {"Financing Tenor Start Date", type date}, {"Financing Tenor", Int64.Type}, {"Maturity Date", type date}, {"Supplier Id", Int64.Type}, {"Supplier Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Financing Tenor", "Maturity Date", "Supplier Id", "Supplier Name"}, {{"Doc Amt", each List.Sum([Document Amt]), type nullable number}, {"Other Ded", each List.Sum([Other Deduction]), type any}, {"Net Amt", each List.Sum([Net Amount]), type nullable number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Supplier Name", "Supplier Id", "Financing Tenor", "Doc Amt", "Other Ded", "Net Amt", "Maturity Date"})
in
    #"Reordered Columns"

Supplier NameSupplier IdFinancing TenorDoc AmtOther DedNet AmtMaturity Date
VIMAL INTERTRADE PVT LTD1862605058305058307/28/2023
PAPER CARTONS1885260334295.42334295.427/28/2023
NEPTUNE HYDROCARBONS MFG (PVT) LTD1860604557730.24557730.27/28/2023
20 Microns Limited1715603194203194207/28/2023
 
Upvote 0
mohadin said:
Hi
To be honest with you I couldn't understand your request
More elaborate please!!
Thsanks
Click to expand...
Hi,

The Summary sheet looks like this:
RBL HUNDI RTGS LETTER 30.05.2023-JEJURI Location.xlsx
ABCDEFGHIJKLM
2228-Apr-23
23
24RBL BANK
25PS ARCADIA,
264A, CAMAC STREET , Kolkata - 700 001
27
28Kind Attn : XX
29
30Dear Sirs,
31
32Sub: Purchase Invoice Discounting
33
34We would request to you to discount all invoices set out in the Appendix hereto, and remit the discounted proceeds to the beneficiaries
35by way of credit to their accounts, details of which are given below.
36
37Interest may be recovered upfront from our account maintained with your bank.
38
39
40Discounting, as requested, is against purchases made by us from various vendors, and are due to be paid to them on dates as mentioned
41 in the “Statement of Invoices” set out in the Appendix hereto.
42
43Proceeds, net of discounting charges, are to be remitted to :
44
45Beneficiary (Vendor)ERP IDTenorTotal Invoice Amount (Rs.)Amount Deductible (Rs.)Net Amount Payable (Rs.)Due DateBeneficiary Bank Name & AddressAccount NumberIFSC Code
46VIMAL INTERTRADE PVT LTD1862605,05,830.000.005,05,830.0028-07-2023abc bankzzzzabcde
47
48Total57,17,275.620.0057,17,275.62
49
50We irrevocably and unconditionally authorize you to debit our current account number xxxxxxxxx maintained with your bank
51 on various due dates towards recovery of the discounted proceeds along with discounting charges at the agreed rate calculated
52on the discounted proceeds for the tenor of discounting.
53
54We also confirm that:
55• No other form of finance has been availed or will be availed against the underlying invoices
56• Goods / Services mentioned in the underlying invoices have been received by us in good order.
RTGS_LETTER 31.05.2023


Your code summarized the data and puts it in Range("A2")
In the predefined format there is only 1 row in summary sheet and suppose the summarized data has more than one rows then i need to insert it manually, can we do it through code.
Here i want it to put the summarized data to Range("A46") and insert as many rows as required through code

Please let me know if you have any query.
 
Upvote 0
HI again
do you mean something like this?
VBA Code: 
Sub test()
    Dim a, w
    Dim i&
    Dim dst As Worksheet
    a = Sheets("Details").Cells(1).CurrentRegion
    Set dst = Sheets("Summary") 
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If Not .Exists(a(i, 13)) Then
                .Add a(i, 13), Array(a(i, 12), a(i, 10), a(i, 4), a(i, 5), a(i, 7), a(i, 11))
                w = .Item(a(i, 13)): w(3) = w(3) + a(i, 6): .Item(a(i, 13)) = w
            Else
                w = .Item(a(i, 13))
                w(2) = w(2) + a(i, 4): w(4) = w(4) + a(i, 7): w(3) = w(3) + a(i, 6) + a(i, 5)
                .Item(a(i, 13)) = w
            End If
        Next
        dst.Rows(46).Resize(i).Insert
        dst.Cells(46, 1).Resize(.Count) = Application.Transpose(.keys)
        dst.Cells(46, 2).Resize(.Count, 6) = Application.Index(.items, 0, 0)
        dst.Cells(46, 1).Offset(.Count + 1) = "Total"
        dst.Cells(.Count + 47, 4) = WorksheetFunction.Sum(Application.Index(.items, 0, 3))
        dst.Cells(.Count + 47, 5) = WorksheetFunction.Sum(Application.Index(.items, 0, 4))
        dst.Cells(.Count + 47, 6) = WorksheetFunction.Sum(Application.Index(.items, 0, 5))
    End With
End Sub
 
Upvote 0
Solution
mohadin said:
HI again
do you mean something like this?
VBA Code: 
Sub test()
    Dim a, w
    Dim i&
    Dim dst As Worksheet
    a = Sheets("Details").Cells(1).CurrentRegion
    Set dst = Sheets("Summary")
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If Not .Exists(a(i, 13)) Then
                .Add a(i, 13), Array(a(i, 12), a(i, 10), a(i, 4), a(i, 5), a(i, 7), a(i, 11))
                w = .Item(a(i, 13)): w(3) = w(3) + a(i, 6): .Item(a(i, 13)) = w
            Else
                w = .Item(a(i, 13))
                w(2) = w(2) + a(i, 4): w(4) = w(4) + a(i, 7): w(3) = w(3) + a(i, 6) + a(i, 5)
                .Item(a(i, 13)) = w
            End If
        Next
        dst.Rows(46).Resize(i).Insert
        dst.Cells(46, 1).Resize(.Count) = Application.Transpose(.keys)
        dst.Cells(46, 2).Resize(.Count, 6) = Application.Index(.items, 0, 0)
        dst.Cells(46, 1).Offset(.Count + 1) = "Total"
        dst.Cells(.Count + 47, 4) = WorksheetFunction.Sum(Application.Index(.items, 0, 3))
        dst.Cells(.Count + 47, 5) = WorksheetFunction.Sum(Application.Index(.items, 0, 4))
        dst.Cells(.Count + 47, 6) = WorksheetFunction.Sum(Application.Index(.items, 0, 5))
    End With
End Sub
Click to expand...
How do I learn to reach your level of proficiency, please do tell.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Find the matching details from 2 workbooks compile it in a sheet and then summarize it and put it in different sheet.
Replies
0
Views
395
deba2020
D
D
  • Question Question
Arrange data!
Replies
4
Views
557
doriannjeshi
D
M
  • Question Question
Index Match function List based on date
Replies
7
Views
326
Fluff
Fluff
W
  • Solved
How many rows between each data point in a column
Replies
3
Views
558
Fluff
Fluff
D
  • Question Question
Using VBA/Macros to Duplicate Sheets and Inserting Individual Rows from Table
Replies
2
Views
903
donnington
D

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

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