Time approximation in Index Match

snejsnej

New Member
Joined
May 3, 2010
Messages
45
Hi there,

I have a column containing a large-ish number of cells (column "A"). Each cell contains a concatenation of a dollar figure (can be pos or neg) & a card number & a datetime (MM/DD/YY hh:ss). Example:

-2.23|6202xxxxxxxxxxxx|08/06/13 10:02

<tbody>
</tbody>

I have generally been successful looking for an exact match in two other columns (columns "B" & "C") using Index Match.

The problem I have is that the minute portion of the datetime group in the string (i.e. the last 2 characters) may be off by 1 minute in columns B & C compared to what is in column A. For example:

Column A: -2.23|6202xxxxxxxxxxxx|08/06/13 10:02
Column B & C: -2.23|6202xxxxxxxxxxxx|08/06/13 10:01

The data from B & C are from a different system: they are written to this system very shortly before being written to the second system (data in column A).

This means that most of the time, the data match. But every so often they don't (i.e. when the data in the first system is written a few milliseconds before the rollover to the next minute).

Is there a way to do a check with a tolerance of 1 minute on the datetime section of the string? Or how could this best be done?

Many thanks,
Jens
 
Last edited:

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Jens,

I don't know of any way to do this with worksheet functions within Index Match. I would be happy to write a user-defined function (UDF) in VBA that would do this and work like Index Match, but need to know how you are using the Index Match. Could you provide the specific formula you are using?

Damon
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Try this array formula (use Ctrl+Shift+Enter and not only Enter):

Code:
=COUNT(MATCH(MID(B2,1,LEN(B2)-5)&{"00:00";"00:01"},
MID($A$2:$A$7,1,LEN($A$2:$A$7)-5)&
TEXT(ABS(TRIM(RIGHT($A$2:$A$7,5))-TRIM(RIGHT(B2,5))),"hh:mm"),0))>0
Markmzz
 

snejsnej

New Member
Joined
May 3, 2010
Messages
45
Hi Damon,

Thanks for getting back. This is the formula I'm using:

=IF(ISNA(INDEX(D:D,(MATCH(C4,E:E,0)))),"---",(INDEX(D:D,(MATCH(C4,E:E,0)))))

J
 

snejsnej

New Member
Joined
May 3, 2010
Messages
45
Thanks Markmzz, I'll give it a shot and will let you know.
 

snejsnej

New Member
Joined
May 3, 2010
Messages
45
Markmzz, this formula is a little above my pay grade... I tried it out but I must have done something wrong.

Here is the formula adapted (hopefully correctly) to my requirements:

Code:
=COUNT(MATCH(MID(D2,1,LEN(D2)-5)&{"00:00";"00:01"},
MID($F$2:$F$3332,1,LEN($F$2:$F$3332)-5)&
TEXT(ABS(TRIM(RIGHT($F$2:$F$3332,5))-TRIM(RIGHT(D2,5))),"hh:mm"),0))>0
Column D contains the value I want to find a match (or close match per the potential 1 minute difference) for in column F. (The columns are different from what I posted to Damon above b/c I inserted an extra column to try your formula out, i.e. everythign has shifted over 1 column.)

When I drag it partway down, it returns FALSE for all of the, even those where I know a match exists.

Any tips?
Thanks
Jens
 

snejsnej

New Member
Joined
May 3, 2010
Messages
45
BTW, column E is the index column consisting of a linear series of 1,2,3... 3403.
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Markmzz, this formula is a little above my pay grade... I tried it out but I must have done something wrong.

Here is the formula adapted (hopefully correctly) to my requirements:

Code:
=COUNT(MATCH(MID(D2,1,LEN(D2)-5)&{"00:00";"00:01"},
 MID($F$2:$F$3332,1,LEN($F$2:$F$3332)-5)&
 TEXT(ABS(TRIM(RIGHT($F$2:$F$3332,5))-TRIM(RIGHT(D2,5))),"hh:mm"),0))>0
Column D contains the value I want to find a match (or close match per the potential 1 minute difference) for in column F. (The columns are different from what I posted to Damon above b/c I inserted an extra column to try your formula out, i.e. everythign has shifted over 1 column.)

When I drag it partway down, it returns FALSE for all of the, even those where I know a match exists.

Any tips?
Thanks
Jens
BTW, column E is the index column consisting of a linear series of 1,2,3... 3403.
Hi Jens,

Here my formula with your modification work. Look at this:

Layout:

Col-C
Col-D
Col-E
Col-F
FALSE
47,14|6937xxxxxxxxxxxx|08/06/13 02:40
1
9,12|4486xxxxxxxxxxxx|08/11/13 04:00
TRUE
-3,29|2120xxxxxxxxxxxx|08/05/13 08:00
2
-3,29|2120xxxxxxxxxxxx|08/05/13 08:00
FALSE
-76,39|8259xxxxxxxxxxxx|08/04/13 02:24
3
-36,26|9957xxxxxxxxxxxx|08/04/13 03:25
FALSE
87,38|5811xxxxxxxxxxxx|08/05/13 04:00
4
26,98|4275xxxxxxxxxxxx|08/09/13 06:00
FALSE
97,65|9462xxxxxxxxxxxx|08/09/13 12:00
5
46,29|7676xxxxxxxxxxxx|08/04/13 04:00
TRUE
0,1|1503xxxxxxxxxxxx|08/03/13 02:10
6
0,1|1503xxxxxxxxxxxx|08/03/13 02:11
FALSE
-79,7|4280xxxxxxxxxxxx|08/06/13 12:00
7
-32,62|5750xxxxxxxxxxxx|08/05/13 02:40
FALSE
-73,22|5550xxxxxxxxxxxx|08/01/13 03:25
8
-27,59|2911xxxxxxxxxxxx|08/02/13 00:00
TRUE
43,07|5273xxxxxxxxxxxx|08/07/13 04:00
9
43,07|5273xxxxxxxxxxxx|08/07/13 03:59
FALSE
-12|6958xxxxxxxxxxxx|08/04/13 04:00
10
-72,15|2875xxxxxxxxxxxx|08/09/13 04:00
FALSE
-73,7|9061xxxxxxxxxxxx|08/03/13 01:30
11
-25,44|6811xxxxxxxxxxxx|08/02/13 01:42
TRUE
-18,03|8366xxxxxxxxxxxx|08/11/13 01:42
12
-85,45|7668xxxxxxxxxxxx|08/05/13 01:36
FALSE
-76,05|6447xxxxxxxxxxxx|08/05/13 06:00
13
-18,03|8366xxxxxxxxxxxx|08/11/13 01:42
FALSE
81,09|9505xxxxxxxxxxxx|08/09/13 06:00
14
39,41|8882xxxxxxxxxxxx|08/05/13 08:00
FALSE
46,84|6460xxxxxxxxxxxx|08/05/13 06:00
15
17,38|6900xxxxxxxxxxxx|08/04/13 04:00
FALSE
36,2|4147xxxxxxxxxxxx|08/03/13 01:50
16
-12,6|7065xxxxxxxxxxxx|08/02/13 02:40
TRUE
39,11|5778xxxxxxxxxxxx|08/03/13 02:24
17
39,11|5778xxxxxxxxxxxx|08/03/13 02:23
FALSE
-97,47|1004xxxxxxxxxxxx|08/02/13 03:00
18
39,11|5778xxxxxxxxxxxx|08/03/13 02:34
*******
*****************************************
*****
*****************************************

<tbody>
</tbody>


Array formula (use Ctrl+Shift+Enter and not only Enter):

Code:
C2-> =COUNT(MATCH(MID(D2,1,LEN(D2)-5)&{"00:00";"00:01"},
MID($F$2:$F$3332,1,LEN($F$2:$F$3332)-5)&
TEXT(ABS(TRIM(RIGHT($F$2:$F$3332,5))-TRIM(RIGHT(D2,5))),"hh:mm"),0)) > 0
Did you press Ctrl+Shift+Enter to enter the formula?

If yes, test the formula with my example below.

Markmzz
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again snejsnej,

Here's that UDF. You can use it in place of the formula you posted like this:

=matchdatetime(C2,E:E,D:D)

It automatically handles the ISNA situation, and it uses a 1 minute 1 second tolerance. It should work even around midnight when there is a turnover of date but the times are still within 1 second.

Here's the code:

Code:
Function MatchDateTime(SearchCell As Range, SearchRange As Range, ReturnRange As Range) As String
   'Searches the SearchRange and returns the cell in the corresponding ReturnRange
   'that matches the value of SearchCell when SearchCell contains a value like
   '"-2.23|6202xxxxxxxxxxxx|08/06/13 10:01" where the date-time part might only
   'match to the nearest minute
   'Returns "---" if no match found.
   
   Dim D       As Date     'the search date-time value
   Dim iRow    As Long
   Dim C       As Range    'cell in SearchRange
   Dim Lstr    As String   'left part of string (not containing date-time)
   Dim BrkChr  As Integer  'character number of 2nd "|" in string
   Dim Dtol    As Date     'date tolerance (nominally one minute+ )
   Dim CLstr   As String
   Dim CD      As Date
   
   Dtol = TimeSerial(0, 1, 1)
   
   BrkChr = Len(SearchCell) - InStr(1, StrReverse(SearchCell), "|") + 1
   Lstr = Left(SearchCell, BrkChr)
   D = CDate(Mid(SearchCell, BrkChr + 1))
   
   For Each C In Application.Intersect(SearchRange, SearchRange.Parent.UsedRange)
      'get left part of string (not including date-time)
      BrkChr = Len(C) - InStr(1, StrReverse(C), "|") + 1
      CLstr = Left(C, BrkChr)
      If CLstr = Lstr Then
         'first part of strings match--check for matching date-time within tolerance
         CD = CDate(Mid(C, BrkChr + 1))
         If Abs(D - CD) < Dtol Then
            'dates also match
            MatchDateTime = ReturnRange(C.Row)
            Exit Function
         End If
      End If
   Next C
   
   'value not found
   MatchDateTime = "---"
     
End Function
Simply paste this code into a standard macro module in your workbook.

Let me know if this works for you. Its going to be a bit slower than the INDEX MATCH, but I'm sure it can be sped up with some re-coding if speed is an issue, such as might be the case if your search range is very large.
 

snejsnej

New Member
Joined
May 3, 2010
Messages
45
Damon and Markmzz, I tried both of your solutions and they both work!

Many thanks for your help, it's greatly appreciated.

Best,
Jens
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,527
Latest member
pro2go

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top