Time approximation in Index Match

[snejsnej]

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:

[TABLE="width: 297"]
<tbody>[TR]
[TD="class: xl64, width: 297"]-2.23|6202xxxxxxxxxxxx|08/06/13 10:02[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Damon Ostrander]

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]

Try this array formula (use Ctrl+Shift+Enter and not only Enter):

=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]

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]

Thanks Markmzz, I'll give it a shot and will let you know.

 

[snejsnej]

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:

=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]

BTW, column E is the index column consisting of a linear series of 1,2,3... 3403.

 

[markmzz]

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:

=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:

[TABLE="width: 370"]
<tbody>[TR]
[TD="class: xl73, width: 42, bgcolor: yellow"]Col-C
[/TD]
[TD="class: xl67, width: 209, bgcolor: transparent"]Col-D
[/TD]
[TD="class: xl67, width: 32, bgcolor: transparent"]Col-E
[/TD]
[TD="class: xl68, width: 209, bgcolor: transparent"]Col-F
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]47,14|6937xxxxxxxxxxxx|08/06/13 02:40
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: transparent"]9,12|4486xxxxxxxxxxxx|08/11/13 04:00
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]TRUE
[/TD]
[TD="class: xl69, bgcolor: #92D050"]-3,29|2120xxxxxxxxxxxx|08/05/13 08:00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl69, bgcolor: #92D050"]-3,29|2120xxxxxxxxxxxx|08/05/13 08:00
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]-76,39|8259xxxxxxxxxxxx|08/04/13 02:24
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: transparent"]-36,26|9957xxxxxxxxxxxx|08/04/13 03:25
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]87,38|5811xxxxxxxxxxxx|08/05/13 04:00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl66, bgcolor: transparent"]26,98|4275xxxxxxxxxxxx|08/09/13 06:00
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]97,65|9462xxxxxxxxxxxx|08/09/13 12:00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl66, bgcolor: transparent"]46,29|7676xxxxxxxxxxxx|08/04/13 04:00
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]TRUE
[/TD]
[TD="class: xl70, bgcolor: #8DB4E2"]0,1|1503xxxxxxxxxxxx|08/03/13 02:10
[/TD]
[TD="class: xl71, bgcolor: #8DB4E2, align: right"]6
[/TD]
[TD="class: xl70, bgcolor: #8DB4E2"]0,1|1503xxxxxxxxxxxx|08/03/13 02:11
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]-79,7|4280xxxxxxxxxxxx|08/06/13 12:00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl66, bgcolor: transparent"]-32,62|5750xxxxxxxxxxxx|08/05/13 02:40
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]-73,22|5550xxxxxxxxxxxx|08/01/13 03:25
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl66, bgcolor: transparent"]-27,59|2911xxxxxxxxxxxx|08/02/13 00:00
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]TRUE
[/TD]
[TD="class: xl70, bgcolor: #8DB4E2"]43,07|5273xxxxxxxxxxxx|08/07/13 04:00
[/TD]
[TD="class: xl71, bgcolor: #8DB4E2, align: right"]9
[/TD]
[TD="class: xl70, bgcolor: #8DB4E2"]43,07|5273xxxxxxxxxxxx|08/07/13 03:59
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]-12|6958xxxxxxxxxxxx|08/04/13 04:00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl66, bgcolor: transparent"]-72,15|2875xxxxxxxxxxxx|08/09/13 04:00
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]-73,7|9061xxxxxxxxxxxx|08/03/13 01:30
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl66, bgcolor: transparent"]-25,44|6811xxxxxxxxxxxx|08/02/13 01:42
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]TRUE
[/TD]
[TD="class: xl69, bgcolor: #92D050"]-18,03|8366xxxxxxxxxxxx|08/11/13 01:42
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12
[/TD]
[TD="class: xl66, bgcolor: transparent"]-85,45|7668xxxxxxxxxxxx|08/05/13 01:36
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]-76,05|6447xxxxxxxxxxxx|08/05/13 06:00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]13
[/TD]
[TD="class: xl69, bgcolor: #92D050"]-18,03|8366xxxxxxxxxxxx|08/11/13 01:42
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]81,09|9505xxxxxxxxxxxx|08/09/13 06:00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl66, bgcolor: transparent"]39,41|8882xxxxxxxxxxxx|08/05/13 08:00
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]46,84|6460xxxxxxxxxxxx|08/05/13 06:00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl66, bgcolor: transparent"]17,38|6900xxxxxxxxxxxx|08/04/13 04:00
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]36,2|4147xxxxxxxxxxxx|08/03/13 01:50
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl66, bgcolor: transparent"]-12,6|7065xxxxxxxxxxxx|08/02/13 02:40
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]TRUE
[/TD]
[TD="class: xl70, bgcolor: #8DB4E2"]39,11|5778xxxxxxxxxxxx|08/03/13 02:24
[/TD]
[TD="class: xl71, bgcolor: #8DB4E2, align: right"]17
[/TD]
[TD="class: xl70, bgcolor: #8DB4E2"]39,11|5778xxxxxxxxxxxx|08/03/13 02:23
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: yellow"]FALSE
[/TD]
[TD="class: xl66, bgcolor: transparent"]-97,47|1004xxxxxxxxxxxx|08/02/13 03:00
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl66, bgcolor: transparent"]39,11|5778xxxxxxxxxxxx|08/03/13 02:34
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*******
[/TD]
[TD="class: xl65, bgcolor: transparent"]*****************************************
[/TD]
[TD="class: xl65, bgcolor: transparent"]*****
[/TD]
[TD="class: xl65, bgcolor: transparent"]*****************************************
[/TD]
[/TR]
</tbody>[/TABLE]


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

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]

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:

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]

Damon and Markmzz, I tried both of your solutions and they both work!

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

Best,
Jens