Return Call time for each person less or equal than Break time

Hi ,

I am new to macro so need your help. i have 2 sheet i sheet consists of advisor name and break time and other sheet consists of the advisor name and their call timing and need to find the last call call of the advisor before each break.


Example

first sheet
A1 B1
Advisor Break Time
Amitav 10:03:00
Amitav 11:03:26
amitav 12:32:00
amitav 13:43:32
MD 9:00:32
MD 10:35:32
MD 12:23:34
MD 14:27:00
jackson 11:34:00
Jackson 14:25:00
Jackson 15:20:35

The second sheet
Advisor Call Time

Amitav 11:03:26
amitav 12:32:00
amitav 13:43:32
amitav 9:00:32
amitav 10:35:32
amitav 12:23:34
amitav 14:27:00
amitav 11:34:00
amitav 14:25:00
amitav 15:20:35


I want to find the last call time of amitav before every break and this for all advisor .

I have a large data so i want to make a loop that will repeat as the advisor name changes.

please rename your thread to something more meaningful, that actually describes your problem. lots of contributors will look at a thread title, and if it of interest to them, or falls within their area of expertise, and will only open those threads. also, searching for "help" will not turn up too many results that will benefit you, sorry

Amitav,

You need to comply with post 3 and edit the title in post 1.

hi amitav. 1 way is to sort by Names & then by Call time, like i did in Sheet 2 column D:E, and then do up the formula

amitav:
- don't open duplicated posts (Rule 5 of Forum rules)
- adopt your title to something meaningfull (Rule 1 of Forum rules)

Please check rules before continue using Forum

I'll merge this one :

http://www.excelforum.com/excel-prog...=1#post3047893

in my solution, i was referencing to column D & E in Sheet2, and only up to 45 rows. you didnt change that. anyway, it seems like my formula was flawed somehow. here's an amended one to paste in Sheet1 C2 & pasted down:

Please Login or Register  to view this content.

here's the lengthy explanation. let's go through simpler eg before we go to the real case. for eg.

Sales	Commission
0	1%
1000	5%
3000	10%

1) LOOKUP
A simple LOOKUP statement will use the lookup_value you indicate & look for an exact match in the lookup_vector. If it doesn't find any, it looks for the closest number lesser than the lookup_value & return the corresponding row in the result_vector. say if we want to give commissions based on the sales figures, then 0 to lesser than 1K will be "1%", 1k to lesser than 3K will be "5%", & 3K or more will be labelled 10%. you can try copying this table into A1:B4 & place a sales number in C1. then in D1:
=LOOKUP(C1,A2:A4,B2:B4)

2) Logical Test
so that's for LOOKUP. secondly, we are just going to do some logical tests. A logical test will return TRUEs & FALSEs. using the above eg again, you can try this in D6:
=A2:A4>500
that is to find which which of these cells in A2:A4 is greater than 500. it gives you a VALUE error because there are multiple values. to see them, press F2 on the cell D6 & press F9 to calculate. you should see this:
={FALSE;TRUE;TRUE}
that's saying A2 is not greater than 500 (FALSE), A3 is greater than 500 (TRUE), A4 is greater than 500 (TRUE)

3) Multiple Logical Tests
In your situation, you need more than 1 logical test; Names in the range must be equals to xxx, time in the range must be greater than or equals to x timing minus 30 minutes, and time in the range cannot exceed the x timing. Excel actually treats TRUE as 1 & FALSE as 0. so if you were to multiply 2 different logical tests, you would get 1s only when both logical tests are TRUE. using the eg again, let's find which is more than 500 & has a commission more than 7%. so in D11:
=(A2:A4>500)*(B2:B4>0.07)
again, VALUE error. but if you press F2 again on the cell & F9 to calculate, you should get:
{0;0;1}
what happened was the 1st logical test returns {FALSE;TRUE;TRUE} & the 2nd {FALSE;FALSE;TRUE}. so converting them to 1s & 0s would be
{0;1;1}*{0;0;1}
so:
{0*0;1*0;1*1}
only the last one would get 1. i placed a table in B16:D19 doing the logical test individually to show you

4) TIME
Time are actually decimals in Excel. the number 1 is one full day, so half a day is actually 0.5 (which is equivalent to 12 pm). hence, to do a subtraction of 30 minutes to a time, you have to use the TIME formula or some division to get a decimal. for eg. i placed a time of 12 pm in
B21. the TIME formula contains 3 parts; Hours, Minutes, Seconds. so if i dont place anything in it, it's actually using 0. i can then use
=B21-TIME(,30,)
or;
=B21-30/60/24
30 minutes has to be divided by 60 minutes an hour & 24 hours a day. of course, you can simply type the total minutes a day:
=B21-30/1440

5) LOOKUP with Logical Tests
As you have seen with the logical tests, TRUEs return 1 & FALSEs return 0. so if i use 1 divided by all the 1s & 0s, i will get a bunch of DIV errors & 1s. DIV error happens when you take a number to be divided by 0. see D24
=1/((A2:A4>500)*(B2:B4>0.07))
calculate & you'll get:
={#DIV/0!;#DIV/0!;1}
so the maximum number i'm ever going to get here is 1. remember what happens what you look up for a number that exceeds the lookup vector (like a sales of $3.5k)? it will return you the last entry. that is why when your data is sorted, it will return you the last timing of person x according to the criteria you state.

6) IF & ISNA
the last bit here is just to return a blank when there are no scenarios that meets the criteria. C29 shows a perfect scenario where there the criteria of >500 & >0.07 is met. C30 shows a scenario where >5000 is not met, so it returns NA. C31 shows a scenario where >0.2 is not met, hence NA. so applying the IF & ISNA can help hide the errors in D29:D31. basically:
=IF(ISNA(FormulaInput),"",FormulaInput)
so the above is saying if the FormulaInput (our LOOKUP formula) is NA, return blank (""), otherwise return the results of FormulaInput (LOOKUP formula).


you can see Sheet2 for a minimised version of your data.

hope that helps