Help with a consolidating data with a dynamic range

[Oberon70]

Hello Everyone,

I appreciate any help that can be provided as I am new to VBA. I want to consolidate data in a table where Column A is the reference number. The other columns are related to payment and commission charges. I am wanting to automate the manual process of me sorting the statement into a format that is easier to receipt from as some statements can have over 2000 transactions. A reference number could appear several times in the statement as it determines how often payment is being received from an individual. I have worked out most of the coding needed to automated this task except for consolidating the data. I have used Record Macro, but won't be able to use this as the range is fixed. The range of data can change depending on how many transactions are in the statement.

Below is a screenshot. On the left side is the unconsolidated data and on the right side is the data that has been consolidated.

Screenshot 2022-01-26 124231.png

I have also attached a spreadsheet with fake information in it, as it shows what I have done and what code I am working within trying to accomplish consolidating the data. The code is in module 3.

Thanks for any help.

[jindon]

Please Login or Register  to view this content.

[Oberon70]

Thank you so much, that works perfectly

[Oberon70]

Hi, I was hoping someone could provide me a breakdown on how the above Macro works? It works great, but I would like to be able to change it for another agent, whose Data file has the information on different columns and I would like to add additional columns to be consolidated.

[jindon]

I don't know if you can add another column(s) for yourself, but this is to give you a better understandings, I hope...

Please Login or Register  to view this content.

Do a step debug to understand better...

[Oberon70]

Does the 1,6,7 and 9 represent the column on Sheet"Data"?

I have attached another spreadsheet, but with sample data that is in a different format.

There is a "Sample" sheet, which shows the results, but what I was trying to achieve.

[jindon]

Seems simple adding each column...

Please Login or Register  to view this content.