If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Need help, multiple criteria with approximate matching

Thread starter biskwit
Start date Sep 21, 2014

B

biskwit

New Member

Sep 21, 2014

#1

Hi Ladies and Gentleman!

I have a problem. On column AL, i want to return "ok" if AI and AJ have a match on AF ang AG.
however i want have it check for approximate time also. Because my formula searches for exact
matches only. Please help me and have it search also in approxiamate value on time of 1 minute.

=INDEX($AK$2:$AK$1650, MATCH(1,(AI2=$AF$2:$AF$1650)*(AJ2=$AG$2:$AG$1650),0))

AF AG AH AI AJ AK AL

Caller

Answer Time

ID

Caller

Calltime

ID

ok=not abandoned

3103875551

<tbody>
</tbody>

<tbody>
</tbody>

4:15:32

<tbody>
</tbody>

<tbody>
</tbody>

ok

3103875551

<tbody>
</tbody>

<tbody>
</tbody>

4:14:28

<tbody>
</tbody>

<tbody>
</tbody>

ok

<tbody>
</tbody>

#N/A

3103875551

<tbody>
</tbody>

<tbody>
</tbody>

4:16:59

<tbody>
</tbody>

ok

3103875551

<tbody>
</tbody>

<tbody>
</tbody>

4:15:53

<tbody>
</tbody>

<tbody>
</tbody>

ok

#N/A

8657481751

<tbody>
</tbody>

0:19:42

<tbody>
</tbody>

ok

8657481751

<tbody>
</tbody>

0:18:38

<tbody>
</tbody>

ok

#N/A

8657481751

<tbody>
</tbody>

0:20:46

<tbody>
</tbody>

ok

8657481751

<tbody>
</tbody>

0:19:42

<tbody>
</tbody>

ok

ok

8657481751

<tbody>
</tbody>

0:21:57

<tbody>
</tbody>

ok

8657481751

<tbody>
</tbody>

0:21:07

<tbody>
</tbody>

ok

#N/A

<tbody>
</tbody>

**on AL5 it returns an "ok" because AI5 and AJ5 found a match on AF4 and AG4.
**i want AL3 to return "ok" also because AI3 and AJ3 is in range with AF2 and AG2 with only 21seconds difference.
**i want a range of 30 seconds or maybe 1 min.

Thank you very much!!!!

Excel Facts

Formula for Yesterday

Click here to reveal answer

Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Sort by date Sort by votes

B

biskwit

New Member

Sep 22, 2014

#2

how to delete this thread? please refer to http://www.mrexcel.com/forum/excel-...-2-criteria-within-30-seconds-time-range.html
my chart was a bit difficult to understand.

Upvote 0

M

Marcelo Branco

MrExcel MVP

Sep 22, 2014

#3

Maybe...

AL2
=IF(SUMPRODUCT(--($AF$2:$AF$1650=AI2),--(ABS($AG$2:$AG$1650-AJ2)<=30/86400)),"Ok","N/A")
copy down

M.

Upvote 0

B

biskwit

New Member

Sep 23, 2014

#4

Hi Marcelo! That was a huge help! really, you dont know how thankful i am. I have a problem regarding this, its actually taking a long time to calculate, its "showing calculating 4 processors". I have close to 20 thousand entries.

Upvote 0

M

Marcelo Branco

MrExcel MVP

Sep 24, 2014

#5

To improve the performance you can put the SUMPRODUCT part in a helper column.
For example put
=SUMPRODUCT(--($AF$2:$AF$1650=AI2),--(ABS($AG$2:$AG$1650-AJ2)<=30/86400))
in AZ2 and copy down.
Select, Copy(Ctrl+C) and Paste only values

Then try this formula
=IF(AZ2,"Ok","N/A")

M.

Upvote 0

B

biskwit

New Member

Sep 24, 2014

#6

The data actually has date, originally it has date and time but i seperated using delimit option. Would it be more efficient if lets an entry made on sep 5 at 3:30 pm,would only search fields with sep 5 dates?

this is like a whole monelth of data consisting of phne call call time stamps and transfer time stamps but none the less i will try that. =)

Upvote 0

M

Marcelo Branco

MrExcel MVP

Sep 24, 2014

#7

biskwit said:
Would it be more efficient if lets an entry made on sep 5 at 3:30 pm,would only search fields with sep 5 dates?

I don't think so.

Try the helper column and see what happens.

M.

Upvote 0

B

biskwit

New Member

Sep 24, 2014

#8

Thanks mr. M! On my way to office. Can i ask another one?

Like on column A consist of date and time.

9/1/2014 12:01 am
9/1/2014 1:12 am
9/1/2014 2:01 am

I want to count the number of entries on column A date and time ranges from 9/1/2014 1:00 am till 9/1/2014 2:00 am only.

Thanks!

Upvote 0

M

Marcelo Branco

MrExcel MVP

Sep 24, 2014

#9

You can use COUNTIFS

Something like

=COUNTIFS(A:A,">="&B2,A:A,"<="&C2)

where B2 houses the Begin Date-Time and C2 the End Date-Time

M.

Upvote 0

B

biskwit

New Member

Sep 24, 2014

#10

Its not returning any result M. Is my date and time format wrong?

Upvote 0

You must log in or register to reply here.

Similar threads

Solved

Move selected row to top of range

Replies: 6

Views: 129

Mar 12, 2024

gtd526

Solved

Consolidate duplicate Names

Replies: 6

Views: 104

Mar 27, 2024

Fluff

Solved

vba selecting entire range from offset

Replies: 5

Views: 167

Jan 28, 2024

Tetra201

Question

vba selecting a specific range if ISNUMBER

Replies: 1

Views: 159

Jan 28, 2024

gtd526

Question

vba Sorting Columns w/ 2 Criteria's

Replies: 1

Views: 137

Mar 7, 2024

gtd526

Share:

Facebook X (Twitter) Reddit Pinterest Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,214,971

Messages: 6,122,521

Members: 449,088

Latest member: RandomExceller01

Share this page

Facebook X (Twitter) Reddit Pinterest Tumblr WhatsApp Email Link

Top