VBA date match by rounding

[NewUser2]

Hi, I'm looking to pull a date (call it D1) from 1 tab, and look it up with the match function (yes, must be the match function because there are other criteria involved). Problem is, the date i am looking up can be Any day of the year, but the table i am looking it up in (call it D2), is only end of month, quarters. (3/31/2017, 06/30/2017, 09/30/2017, 12/31/2017)

Here is what I have:

D1 = Worksheets("Sheet1").Cells(y, 27)
row = Find(Postal, Type1, D1) 'as you can see here, postal and type are the other criteria i am looking for



'Here cVal = D1 and c = D2 column (and yes, this find function isn't mine, got it from a thread on here)
Function Find(aVal As String, bVal As String, cVal) As Long
Dim maxRow As Long
Dim row As Long
maxRow = Range("A100000").End(xlUp).row
For row = 3 To maxRow
Dim a As String
a = ActiveSheet.Cells(row, 3).Value
b = ActiveSheet.Cells(row, 4).Value
c = ActiveSheet.Cells(row, 5).Value
If a = aVal And b = bVal And c = cVal Then
Find = row
Exit Function
End If
Next row
Find = -1
End Function



Also please note.... that my code runs through this thousands of time and is very time consuming, given the size of the database. Any suggestions that would make this faster would be greatly appreciated! (end goal is to name criteria (looping from 1-3000) on 1 tab, then look it up on another tab (1 at a time)) and result is a number that is offset from the matched numbers.

 

[StephenCrump]

The code you have posted will be very slow because it is reading values cell by cell - there are much faster ways to do this.

But why not take a step back and tell us exactly what you are trying to do, because it is not at all clear.

You also say that the solution must include the Match function. That's an assumption that may not be correct.

What does you data look like? What results are you trying to achieve?

 

[NewUser2]

Okay so I have 2 sheets (sheet1 and sheet2). They both have loads of data, Sheet1 is my data, sheet2 is from an external source. Each row in Sheet1 represents a unique entry with columns representing it's characteristics. Sheet2 will have some entries that have similar characteristics to sheet1 and I want to find this entry by matching several criteria to criteria in Sheet1 and bring a piece of information over from sheet2 to sheet1.

The part I am struggling with is the date. Sheet2 Only lists dates in it's columns that are Quarter ends (example: 3/31/2012, 06/30/2015, 09/30/2016, 12/31/2017). But the dates in Sheet1 will be any day of any year. Also, having the date match to the nearest option is probably the most important part of this because i need to value something based on the nearest date. So that said, it would Also be ideal if it rounded based on which day it is closest to. I've been able to accomplish changing my date to match quarters in an excel formula, but it always rounds up.

- Example of what I want for the date match: if Sheet1 has: 4/2/2017, I would like it to match with 3/31/2017, not 06/30/2017.
- Example of the formula I have in excel (looking for a more efficient method in vba): MATCH(1,(EOMONTH(4/2/2017,MOD(12-MONTH(4/3/2017),3))=Sheet2!$E:$E)

That's probably much more confusing.. but yeah i guess saying that it must use Match is probably a bad assumption.
Hopefully someone can help!
Thanks!

 

[StephenCrump]

That's a little clearer, thanks.

The nearest EOQ part should be easy. But what about 15 Aug and 15 Nov which fall in the middle of the quarter. Do you want to round these dates down 46 days (to the 30 Jun or 30 Sep EOQ respectively) or up 46 days (to 30 Sep or 31 Dec)?

The EOQ dates on Sheet2: Are these sorted in any particular order? Given you are talking about matching other criteria, my guess is that there might be multiple lines for any given EOQ date, such as 30 June 2018, and you want to the nearest EOQ date that also matches these other criteria? Can you give us an example of another criterion?

We sometimes see the drip feed approach on this forum, where the original poster keeps coming back to say: thanks, but your suggestion doesn't work because there's this additional complication that I hadn't told you about before.

But if you fully define your problem up front (and it's within reason), there are many smart people on the forum who will fall over themselves to give you clever solutions.

 

[NewUser2]

Good question about dates falling right in the middle. In an ideal world I'd get a weighted average of the results that fall on each of the quarter ends on either end of my lookup date. But that is likely too complicated.

In another thread Rick Rothstein gave me this good code for the EOQ date rounding issue (not a weighted average), but a good solution nonetheless:
Sub LastDayRoundedToNearestQuarter() Dim Dte As Date, Appdate As Date, MidQuarter As Date Dte = Sheets("Sheet1").Range("D1") MidQuarter = Int(Application.Average(DateAdd("q", Format(Dte, "q"), "1/1/" & Year(Dte)), DateAdd("q", Format(Dte, "q") - 1, "1/1/" & Year(Dte)))) Appdate = DateSerial(Year(Dte), 3 * (Format(Dte, "q") + (Dte < MidQuarter)) + 1, 0)End Sub


Now to figure out the *best way to loop through all my entries (whether it's a loop or not). So the other data I am looking up are sections of words, and not in any particular order. The only thing that is ordered are the dates on Sheet2. Using the custom 'Find' approach mentioned in comment 1 works, but it's just extremely slow. I'm wondering if creating some sort of array or something would work better...

Here is an example of the data:

[TABLE="width: 557"]
<tbody>[TR]
[TD="colspan: 3"]Sheet2
[/TD]
[TD][/TD]
[TD]My data
[/TD]
[TD]Sheet1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Misc lookup field
[/TD]
[TD]Dates
[/TD]
[TD]Misc2
[/TD]
[TD][/TD]
[TD]Match to sheet2
[/TD]
[TD]

Date to lookup
[/TD]
[TD]Result from lookup
[/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]12/31/2016
[/TD]
[TD]241.87
[/TD]
[TD][/TD]
[TD]Tim
[/TD]
[TD]4/10/2017
[/TD]
[TD]254.28
[/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]3/31/2017
[/TD]
[TD]254.28
[/TD]
[TD][/TD]
[TD]John
[/TD]
[TD]4/11/2017
[/TD]
[TD]245.28
[/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]6/30/2017
[/TD]
[TD]289.97
[/TD]
[TD][/TD]
[TD]tim
[/TD]
[TD]6/10/2018
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]9/30/2017
[/TD]
[TD]269.93
[/TD]
[TD][/TD]
[TD]john
[/TD]
[TD]7/20/2017
[/TD]
[TD]91
[/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]12/31/2017
[/TD]
[TD]257.77
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]3/31/2018
[/TD]
[TD]96.93
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]6/30/2018
[/TD]
[TD]100.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]9/30/2018
[/TD]
[TD]101.48
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]12/31/2016
[/TD]
[TD]232.87
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]3/31/2017
[/TD]
[TD]245.28
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]6/30/2017
[/TD]
[TD]280.97
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]9/30/2017
[/TD]
[TD]260.93
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]12/31/2017
[/TD]
[TD]248.77
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]3/31/2018
[/TD]
[TD]87.93
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]6/30/2018
[/TD]
[TD]91.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]9/30/2018
[/TD]
[TD]92.48
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks,

That's a little clearer, thanks.

The nearest EOQ part should be easy. But what about 15 Aug and 15 Nov which fall in the middle of the quarter. Do you want to round these dates down 46 days (to the 30 Jun or 30 Sep EOQ respectively) or up 46 days (to 30 Sep or 31 Dec)?

The EOQ dates on Sheet2: Are these sorted in any particular order? Given you are talking about matching other criteria, my guess is that there might be multiple lines for any given EOQ date, such as 30 June 2018, and you want to the nearest EOQ date that also matches these other criteria? Can you give us an example of another criterion?

We sometimes see the drip feed approach on this forum, where the original poster keeps coming back to say: thanks, but your suggestion doesn't work because there's this additional complication that I hadn't told you about before.

But if you fully define your problem up front (and it's within reason), there are many smart people on the forum who will fall over themselves to give you clever solutions.

 

[StephenCrump]

In an ideal world I'd get a weighted average of the results that fall on each of the quarter ends on either end of my lookup date. But that is likely too complicated.

So perhaps:

Names: =A2:A18
EOQs: =B2:B18
Amounts: =C2:C18
Helper G2: =DATE(YEAR(F2),CEILING(MONTH(F2),3)+1,0)
Helper H2: =EOMONTH(G2,-3)
I2: =IFERROR((IFERROR(INDEX(Amounts,MATCH(1,(Names=E2)*(EOQs=G2),)),0)*(F2-H2)+IFERROR(INDEX(Amounts,MATCH(1,(Names=E2)*(EOQs=H2),)),0)*(G2-F2))/(ISNUMBER(INDEX(Amounts,MATCH(1,(Names=E2)*(EOQs=G2),)))*(F2-H2)+ISNUMBER(INDEX(Amounts,MATCH(1,(Names=E2)*(EOQs=H2),)))*(G2-F2)),"Not found!") Array-entered

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Date	Amount		Match	Date	EOQ	Prev EOQ	Result
2	Tim	31 Dec 16	241.87		Tim	10 Apr 17	30 Jun 17	31 Mar 17	258.20
3	Tim	31 Mar 17	254.28		John	11 Apr 17	30 Jun 17	31 Mar 17	249.59
4	Tim	30 Jun 17	289.97		Tim	10 Jun 18	30 Jun 18	31 Mar 18	99.33
5	Tim	30 Sep 17	269.93		John	20 Jul 17	30 Sep 17	30 Jun 17	276.61
6	Tim	31 Dec 17	257.77		John	15 Oct 18	31 Dec 18	30 Sep 18	16.30
7	Tim	31 Mar 18	96.93		John	30 Sep 18	30 Sep 18	30 Jun 18	0.00
8	Tim	30 Jun 18	100.00		John	15 Dec 18	31 Dec 18	30 Sep 18	82.61
9	Tim	30 Sep 18	101.48		John	06 Jan 19	31 Mar 19	31 Dec 18	100.00
10	John	31 Dec 16	232.87		John	03 Apr 19	30 Jun 19	31 Mar 19	Not found!
11	John	31 Mar 17	245.28
12	John	30 Jun 17	280.97
13	John	30 Sep 17	260.93
14	John	31 Dec 17	248.77
15	John	31 Mar 18	87.93
16	John	30 Jun 18	91.00
17	John	30 Sep 18	0.00
18	John	31 Dec 18	100.00
Sheet1

 

[StephenCrump]

So the other data I am looking up are sections of words, and not in any particular order.

I just noticed this extra detail ....

Do you want to define the whole problem?