Oberon70
Board Regular
- Joined
- Jan 21, 2022
- Messages
- 160
- Office Version
- 365
- Platform
- Windows
Hi,
I have the below code that was done for me and it works great.
I have been slowing starting to understand what the code does.
the code combines together the amounts that have the same reference number and outputs it to where I tell it.
The questions is that I have Data in other cells that I would like to be included in the new table , it only has to be copied one for each reference number.
example result for the above on the table would be
The new table also doesn't have all the columns that were in the statement, but I get the transactions for the same reference on the same row, but the dollars amounts are summed together.
I hope this make since. sorry.
I have the below code that was done for me and it works great.
VBA Code:
Sub Data_Shrink_NM()
Dim a, i As Long, ii As Long, w
With ClosedBook.Sheets("data").Cells(1).CurrentRegion
a = Application.Index(.Value, Evaluate("row(2:" & .Rows.Count & ")"), Array(1, 11, 15, 16, 17))
End With
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(a, 1)
If Not .exists(a(i, 1)) Then
ReDim w(1 To UBound(a, 2))
w(1) = a(i, 1)
Else
w = .Item(a(i, 1))
End If
For ii = 2 To UBound(a, 2)
w(ii) = w(ii) + a(i, ii) '<-- adding each column from 2nd to the last
Next
.Item(a(i, 1)) = w
Next
a = Application.Index(.items, 0, 0)
End With
With wb_created.Sheets("To Be Receipted").[A2] '<--- change n2 to site for output cell.
With .Resize(UBound(a, 1), UBound(a, 2))
.Value = a
.Columns(4).NumberFormatLocal = "0.00"
End With
End With
End Sub
I have been slowing starting to understand what the code does.
ATO27278 | WayneTec2L | 4/01/2022 | 1241830 | 19728 | 27/11/2016 | Customer 1 | 50 | 17.5 | 1.75 | 0 | 30.75 | 18/04/2018 | SPA2901-01 | ||||||
ATO27278 | WayneTec2L | 17/01/2022 | 1241830 | 19741 | 27/11/2016 | Customer 1 | 50 | 17.5 | 1.75 | 0 | 30.75 | 18/04/2018 | SPA2901-01 |
the code combines together the amounts that have the same reference number and outputs it to where I tell it.
The questions is that I have Data in other cells that I would like to be included in the new table , it only has to be copied one for each reference number.
example result for the above on the table would be
Client Ref | Client | Date | Our Ref | Portfolio Sort | DOD | Insurer Ref | Insured Name | PAYT - DEB Amt | PAYT - COMM | PAYT - GST | Net Return | Date Entered | Invoice No. |
ATO27278 | WayneTec2L | 4/01/2022 | 1241830 | 19728 | 27/11/2016 | Customer 1 | 100 | 35 | 3.5 | 61.5 | 18/04/2018 | SPA2901-01 |
The new table also doesn't have all the columns that were in the statement, but I get the transactions for the same reference on the same row, but the dollars amounts are summed together.
I hope this make since. sorry.