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
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
Last edited: