Find latest record and update the data

I have a large spreadsheet that contain client details. Each row represents a client and each column a field associated with the client. e.g. name, address, Payment, Balance.
Each month a new Spreadsheet gets added to the bottom of the existing spreadsheet (changes in month). So after a year the spreadsheet will contain 12 months of transactions.

What I need to do is consolidate these multiply transactions for each client so they only represent one row of data in the spreadsheet.

Example
BEFORE

Unique ref Transaction Date Trans Type Name Address Balance Payment
1957654 12/10/14 New Business Mr Smith 23 Raven Road $40,000 $50.00
1957654 12/11/14 Adjustment Mr Smith 23 Raven Road -$15,000 -$20.00
1957654 12/12/14 Adjustment Mr Smith 23 Raven Road $4,000 $5.00

RESULT

Unique ref Transaction Date Trans Type Name Address Balance Payment
1957654 12/10/14 New Business Mr Smith 23 Raven Road $29,000 $35.00


However, Renewal should restart the Consolidation.
So,
Unique ref Transaction Date Trans Type Name Address Balance Payment
1957654 12/10/14 New Business Mr Smith 23 Raven Road $40,000 $50.00
1957654 12/11/14 Adjustment Mr Smith 23 Raven Road -$15,000 -$20.00
1957654 12/12/14 Adjustment Mr Smith 23 Raven Road $4,000 $5.00
1957654 12/10/15 Renewal Mr Smith 23 Raven Road $30,000 $40.00

Should finish like this.
Unique ref Transaction Date Trans Type Name Address Balance Payment
1957654 12/10/14 New Business Mr Smith 23 Raven Road $29,000 $35.00
1957654 12/10/15 Renewal Mr Smith 23 Raven Road $30,000 $40.00

I think its important to consider the 'Unique reference' as the identifier together with a new field called 'year of account'
So
1957654 will become 14-1957654 for 2014 and 15-1957654 for 2015. This will identify what transactions need to be consolidated so we are not consolidating a 'life of a policy' into one line but consolidating yearly.

Next would be to use the most recent 'transaction date' and then consolidate.

However,
Transactions types.
Lapse - Should Delete all transactions as client row no longer live
Cancelled - Should Delete all transactions as client row no longer live

If the total 'Balance' = 0.00 then also - Delete all transactions as client row no longer relevant. As a balance can't be 0 once consolidated.

THOUGHT PPROCESS
I was thinking the best approach possibly, When the workbook is open, create a 'virtual' SQL database, so the calculation is done in there and then it spits out the results on a new tab in excel.

For the other fields always use the latest fields supplied unless a field is blank then use the one previous. So if a field has been left blank one month it carries the field over from the previous month. Otherwise someone could delete say the clients address in the current month and we will no longer have it going forward?


------------------------------------------------------------------
Finally I have another spreadsheet that needs to do the above, but the transactions do not need to be consolidated but only the latest live risk shown.
(Should this be another thread? - I only include as I thought it might be similar coding)

Really appreciate anybodies input / time. It is a very complex task/build I am asking for.

Please see attached an example workbook of what i am trying to achieve. The actual spreadsheet has about 30 Columns and 1000s of rows of data. I have create a test template.

At the moment this task is being done manually and I take 2 weeks to complete it each month as it needs to be done 24 times. So a new process would be welcomed.

Please use the attached table in conjunction with my description in the first thread.

Appreciate any help.

If anyone could offer even part code for what I am looking to achieve. It would be greatly appreciated.

Is this how you wanted

Please Login or Register  to view this content.

Hi jindon

Thank you for the code.

I have tried this with some other test data. Please see attached. Can you please advise why the code is not combining the attached transactions.

Also It is not showing a 'Renewal' If there is no 'New business'? As you can appreciate this will be introduced where clients will only have a 'Renewal' transaction as the original 'New Business' is on another system somewhere.

So a client may only have a Renewal. Or they may have been with us for 3 years so had 3 Renewals.

The end Result needs to Show All 'New Business' and All 'Renewals' if they are still live as at a date set by me.
While combining all 'Adjustment' transactions in each period.
Deleting Any Lapse or Cancelled Clients.


Are you able to put notes on your code so I can understand each Step? Sorry to be a pain. - Appreciate your help.

Sorry, this is a better attachment to go by as it contains the original data we have been discussing and easier to understand what result I want.

Let me know if I need to explain a bit more clearly as it is confusing and hard to explain.

Thanks again.

Still not really sure...

Don't understand the reason why
3484348 2015 in your result is "New Business"....not "Adjustment"...

Try this one

Please Login or Register  to view this content.

Hi jindon This is now pulling through all the test records. However it is not calculating columns 'J' - 'Balance' and 'K' - 'Payment'
It is only showing the current row.
Example '1223456' for the '2014' Year should be $74.50 in Column 'K'. Which is $100-$30+4.50
and Column J should be $33,000

I need like a SUM(Balance), Sum (Payment) type of formula somewhere in this.


The Reason '3484348' Shows 'New Business' is because I need to run a report after to show how much of the business is 'New Business' and How much is Renewal' If I show 'Adjustment' I will not know if this is a 'Renewal' or 'New Business' when I run the report.


Are you able to put comments in next to each line, so I can understand the code please.

Thank you again. Appreciate your time and help

See if this is how you wanted

Please Login or Register  to view this content.

Regarding comment, point out where you don't understand.
I don't want to explain from the very basics of how Array, Dictionary works....

Hi jindon
This is working. Thank you. Is there a way to code to say if the balance matches the adjustment then remain the same.

Example - If New Business the Balance is £50,000 and Payment is £30.00

The following month the payment is additional £25.00 the Row show still show £50,000 and the payment £55.00?

Otherwise each month where there is a change elsewhere it will keep adding $50,000.

Basically if Balance = Balance previous remain the same, Else calculate.


Code is working great. Understanding the code more now. I'm new to learning VBA. very interesting and clever.

Thanks again.

Arigatōgozaimashita

Can you upload a sample workbook, showing what you are asking?

I have this working. Thank you so much. Will save a lot of time.