Modify Array to include txt data and out put to specific columns?

Oberon70

Board Regular
Joined
Jan 21, 2022
Messages
160
Office Version
  1. 365
Platform
  1. Windows
Hi,

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.

ATO27278WayneTec2L
4/01/2022​
1241830​
19728​
27/11/2016​
Customer 1
50​
17.5​
1.75​
0​
30.75​
18/04/2018​
SPA2901-01
ATO27278WayneTec2L
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 RefClientDateOur RefPortfolio SortDODInsurer RefInsured NamePAYT - DEB AmtPAYT - COMMPAYT - GSTNet ReturnDate EnteredInvoice No.
ATO27278WayneTec2L
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Forum statistics

Threads
1,223,367
Messages
6,171,671
Members
452,416
Latest member
johnog

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