Solved: Extract Data Matched Year Only

[tlchan]

I found a useful vba which list out all selected data based on match one or two value in the data sheet. I have customised the to suit my needs for summary data selected from my data for annual report. I have sheet("Data") as my data and sheet("Report") for my summary annual report.


My problem is the code only works on exact matched value in data sheet. What I try to accomplish is variable in range J1 under sheet("Report") is year instead of date eg when J1 in sheet("Report") is year 2010 then the data extracted should be from 1/01/2010 to 31/12/2010.

sample workbook attached

Any help ?

[Tinbendr]

Try changing the format of J1 to a number.

then

[vba]If rCell.Value >= "01/01/" & .Range("J1").Value Or rCell.Value <= "31/12/" & .Range("J1").Value Then[/vba]

David

[macropod]

Hi tlchan,

Some issues to consider:
1. Your code is for a Worksheet_SelectionChange, but you've entered is as being for a Worksheet_Change.
2. To run efficiently, you should keep as much code as practicable outside the loop.
3. To enable the code to extract data spanning a range of dates (eg a financial year), you need to tell it what the start and end dates are. Your code only works for the single specified date.
4. It is good practice to set 'Option Explicit', so as to require all variables to be declared before they're used. Amongst other things, this guards against typos when using variables.

Try replacing your code with this:
[vba]Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rCell As Range, MyRange As Range
Dim Counter As Long, DestRow As Long
Dim wsData As Worksheet, wslist As Worksheet
Dim StartDate As Date, EndDate As Date
Application.ScreenUpdating = False

'Set the names of the worksheets to work with
Set wsData = Sheets("Custdata")
Set wslist = Sheets("Report")
'Set first row of destination
DestRow = 6
'Check if ref changed
If Not Intersect(Target, Range("J1")) Is Nothing Then
'Get range to work with
Set MyRange = wsData.Range("M3:M" & wsData.Range("M" & Rows.Count).End(xlUp).Row)
With Sheets("report")
StartDate = DateSerial(Format(ActiveSheet.Range("J1").Text, "yyyy"), "1", "1")
EndDate = DateSerial(Format(ActiveSheet.Range("J1").Text, "yyyy") , "12", "31")
'Clear old data
.Range("A6:P5000").ClearContents
'Do all rows of data
For Each rCell In MyRange
If rCell.Value >= StartDate Then
If rCell.Value <= EndDate Then
Counter = Counter + 1
.Range("A" & DestRow).Value = Counter
.Range("B" & DestRow).Value = rCell.Offset(, -11).Value
.Range("C" & DestRow).Value = rCell.Offset(, 1).Value
.Range("E" & DestRow).Value = rCell.Offset(, 3).Value * 12
.Range("H" & DestRow).Value = rCell.Offset(, -7).Value
.Range("F" & DestRow).Value = rCell.Offset(, -12).Value
.Range("G" & DestRow).Value = rCell.Offset(, 0).Value
.Range("I" & DestRow).Value = rCell.Offset(, 2).Value
.Range("J" & DestRow).Value = rCell.Offset(, -1).Value
.Range("K" & DestRow).Value = rCell.Offset(, -9).Value
.Range("M" & DestRow).Value = rCell.Offset(, -5).Value
.Range("N" & DestRow).Value = rCell.Offset(, -3).Value
.Range("O" & DestRow).Value = rCell.Offset(, -2).Value
DestRow = DestRow + 1
End If
End If
Next
End With
End If
'Destroy objects
Set rCell = Nothing
Set MyRange = Nothing
Set wsData = Nothing
Set wslist = Nothing
Application.ScreenUpdating = True
End Sub[/vba]

[tlchan]

Thanks Macropod and David for the quick solution. Both solutions work fine. However after extraction of the relevant data I had a situation whereby under column D of my report now need to fill with balance of the amount from column C which computed as follows:

eg cell D6 = C6-(C6*XY/E6) where XY is number of times repay during same reporting year (year under column D same with column H in sheet("collectiondata")

eg as at year end as per J1, Peter Chia balance in Column C will be
20,000-(20,000X1/36) = 19,444

How to fill up this column using VBA based on above condition?

Sample report attached

[macropod]

Hi tlchan,

Your 'collectiondata' worksheet has only one entry per client, and nothing to indicate the number of times per year a payment was made. Hence it appears they all made only one payment. In that case, you could use:
[VBA].Range("D" & DestRow).Value = Round(.Range("C" & DestRow).Value * (.Range("E" & DestRow).Value - 1) / .Range("E" & DestRow).Value)[/VBA]
after the line:
.[VBA]Range("E" & DestRow).Value = rCell.Offset(, 3).Value * 12[/VBA]
I suspect that's really not what you want (the result isn't the same as what you get by simply subtracting the amount on 'collectiondata' from the from the amount in column C on the 'report' sheet), but you haven't given us anything more to work with.

[tlchan]

Hi macropod,
yes the suggested code worked partially correct. I need to frequency no of repayment make by the client in collectiondata worksheet. There will be more than 1 payment for current year and subsequent year for each client.

What I try to achieve to extract for theorical computation in column D of report worksheet based on no of counts/payment of that particular client who make payment in the same year the loan is given.

In the example 'Alan Yong' who given tha loan in same year and also make 2 payments in the same year. Thus the value in column D of report worksheet should be 28,333.


I hope the above clarification to enable you to grasp the overview.
Your help is greatly appreciated.

Thanks

[macropod]

Hi tlchan,

If I understand it correctly, the 'Collectiondata' sheet has the records of actual payments made. In that case, we can get the actual balance rather than one based on some theoretical average. Even with the data in your workbook, if only one payment is assumed, the theoretical balance differs from the calculated one.

To get the actual balance, we need to define a new variable:
[vba]Dim LastRow As Long[/vba] and point it to the last row on the 'Collectiondata' sheet:
[vba]LastRow = Sheets("Collectiondata").Range("A" & Rows.Count).End(xlUp).Row[/vba] which I'd add just after the 'Application.ScreenUpdating = False' line.

Then, instead of the code I gave you in my last post, you'd use:
[vba].Range("D" & DestRow).FormulaArray = "=R" & DestRow & "C3-SUM(IF((Collectiondata!R1C2:R" _
& LastRow & "C2=R" & DestRow & "C2)*(Collectiondata!R1C4:R" & LastRow & "C4>=" _
& Format(StartDate, "000000") & ")*(Collectiondata!R1C4:R" & LastRow & "C4<=" _
& Format(EndDate, "000000") & "),Collectiondata!R1C5:R" & LastRow & "C5))"
.Range("D" & DestRow).Value = .Range("D" & DestRow).Value[/vba] The reason for having two goes at column D is that the first four lines insert an array formula (comment out the last line and you'll see it in the output cells) and the last line converts that to the returned value.

Note: In coding and code performance terms, getting the actual balance takes about the same effort as calculating a theoretical one.

[tlchan]

Hi macropod,

Thanks again for your code. It is good to have actual balance on loan given in current calendar year with repayment within the same calendar year.

In fact in this case the report only require theoretical balance as actual payment has include of other payments. The report only to list the principle loan given out and principle balance only where it was determined by no of payments make.

Actually there are 2 reports to be prepare where report 1 with loan given out on the same calendar year and principle repay on same calendar year.

Report 2 with the same format and to show current theoretical balance (based on culmulative principle repayment) for loan given out in other than current calendar year.

As such, the actual repayment amount is not taken into calculation of balance. What we need is the total number of payment make which to try to get from collectiondata sheet for computation of theoretical balance in 2 type of reports.


I hope you can understanding the report scenario.

Thanks

[macropod]

Hi tlchan,

So where is the number of payments (theoretical or actual) during the year stored? There is nothing I can see on any of the worksheets you've supplied to indicate what that is or how it might be estimated.

[tlchan]

Hi Macropod,
What if we add 1 column (I) named as principle repayment under collectiondata sheet as the number will be fixed for each client say 80 where this numder will be add to the column each time a actual payment is payment is collected. The value from this column can be used to replace the amount collected in your previous code will just work the intended result. However I'm not familiar with your arrayformula in your previous.

Can assist? Thanks

[macropod]

Hi tlchan,

You could use either a '1' or the calculated average theoretical payment value for the client. Either will do. Provided you say which one it will be, coding will be quite easy - and the array formula won't be needed - a formula quite similar to the one I gave you before could be used.

[tlchan]

Hi Macropod,
I think array formula is more appropriate for the intended result. I amended your array formula just try on error but was stuck to get the correct result.

The data extract should be the date under H is the same calendar year as under D. I try out J1 with year 2010 I would get the result but when I try it with year 2009 the result is wrong (eg Name Chua data should not be appear as loan date and paid date is of different calendar year).

Can assist to correct the array formula ? sample workbook attached

Thank you

[macropod]

Hi tlchan,

If the values in column I on the 'Collectiondata' worksheet represent the payment averages, you can simply change:
Collectiondata!R1C5:R" & LastRow & "C5
to:
Collectiondata!R1C9:R" & LastRow & "C9
in the array formula I gave you.

[tlchan]

Hi Macropod,

You are great! I'm now able to accomplish my task.

Thank you

[parttime_guy]

Hi tlchan & macropod,

I have been following this thread - but can you please post the final code, Iam a little lost.

Thanks and Best regards

[tlchan]

Hi Parttime_guy

Attached the final code. Thanks