VBA date match by rounding

NewUser2

Board Regular
Joined
Jan 27, 2015
Messages
54
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,353
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 27, 2015
Messages
54
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!
 
Last edited:

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,353
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 27, 2015
Messages
54

ADVERTISEMENT

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:

Sheet2
My data
Sheet1
Misc lookup field
Dates
Misc2
Match to sheet2


Date to lookup

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

<tbody>
</tbody>


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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,353
Office Version
  1. 365
Platform
  1. Windows
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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;color: #333333;;">Name</td><td style="font-weight: bold;text-align: center;color: #333333;;">Date</td><td style="font-weight: bold;text-align: right;color: #333333;;">Amount</td><td style="text-align: right;color: #333333;;"></td><td style="font-weight: bold;color: #333333;;">Match</td><td style="font-weight: bold;text-align: center;color: #333333;;">Date</td><td style="font-weight: bold;text-align: right;;">EOQ</td><td style="font-weight: bold;text-align: right;;">Prev EOQ</td><td style="font-weight: bold;text-align: right;color: #333333;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">31 Dec 16</td><td style="text-align: right;;">241.87</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">10 Apr 17</td><td style="text-align: right;;">30 Jun 17</td><td style="text-align: right;;">31 Mar 17</td><td style="text-align: right;;">258.20</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">31 Mar 17</td><td style="text-align: right;;">254.28</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">John</td><td style="text-align: center;;">11 Apr 17</td><td style="text-align: right;;">30 Jun 17</td><td style="text-align: right;;">31 Mar 17</td><td style="text-align: right;;">249.59</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">30 Jun 17</td><td style="text-align: right;;">289.97</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">10 Jun 18</td><td style="text-align: right;;">30 Jun 18</td><td style="text-align: right;;">31 Mar 18</td><td style="text-align: right;;">99.33</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">30 Sep 17</td><td style="text-align: right;;">269.93</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">John</td><td style="text-align: center;;">20 Jul 17</td><td style="text-align: right;;">30 Sep 17</td><td style="text-align: right;;">30 Jun 17</td><td style="text-align: right;;">276.61</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">31 Dec 17</td><td style="text-align: right;;">257.77</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">John</td><td style="text-align: center;;">15 Oct 18</td><td style="text-align: right;;">31 Dec 18</td><td style="text-align: right;;">30 Sep 18</td><td style="text-align: right;;">16.30</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">31 Mar 18</td><td style="text-align: right;;">96.93</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">John</td><td style="text-align: center;;">30 Sep 18</td><td style="text-align: right;;">30 Sep 18</td><td style="text-align: right;;">30 Jun 18</td><td style="text-align: right;;">0.00</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">30 Jun 18</td><td style="text-align: right;;">100.00</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">John</td><td style="text-align: center;;">15 Dec 18</td><td style="text-align: right;;">31 Dec 18</td><td style="text-align: right;;">30 Sep 18</td><td style="text-align: right;;">82.61</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #333333;;">Tim</td><td style="text-align: center;;">30 Sep 18</td><td style="text-align: right;;">101.48</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">John</td><td style="text-align: center;;">06 Jan 19</td><td style="text-align: right;;">31 Mar 19</td><td style="text-align: right;;">31 Dec 18</td><td style="text-align: right;;">100.00</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="color: #333333;;">John</td><td style="text-align: center;;">31 Dec 16</td><td style="text-align: right;;">232.87</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">John</td><td style="text-align: center;;">03 Apr 19</td><td style="text-align: right;;">30 Jun 19</td><td style="text-align: right;;">31 Mar 19</td><td style="text-align: right;;">Not found!</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="color: #333333;;">John</td><td style="text-align: center;;">31 Mar 17</td><td style="text-align: right;;">245.28</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="color: #333333;;">John</td><td style="text-align: center;;">30 Jun 17</td><td style="text-align: right;;">280.97</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="color: #333333;;">John</td><td style="text-align: center;;">30 Sep 17</td><td style="text-align: right;;">260.93</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="color: #333333;;">John</td><td style="text-align: center;;">31 Dec 17</td><td style="text-align: right;;">248.77</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="color: #333333;;">John</td><td style="text-align: center;;">31 Mar 18</td><td style="text-align: right;;">87.93</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="color: #333333;;">John</td><td style="text-align: center;;">30 Jun 18</td><td style="text-align: right;;">91.00</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="color: #333333;;">John</td><td style="text-align: center;;">30 Sep 18</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="color: #333333;;">John</td><td style="text-align: center;;">31 Dec 18</td><td style="text-align: right;;">100.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:0.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />
 
Last edited:

Forum statistics

Threads
1,148,108
Messages
5,744,874
Members
423,907
Latest member
zerocool88

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
Top