Hi Everyone. This is my very first post so please take it easy on me
I am sending out alerts to my staff and getting responses. I need to keep track of their response time and if there was one at all.
I need help with the Index Match Function to give me the closest "received time" so I can properly flag the row if they employee has not responded.
Please see attached example.
Thank you!
Cannot discern what you are attempting to do from your file. It appears you want help with column D. How does it relate to data in columns A through C (column A because it has duplicate names).
FWIW, as they currently are, column E and F do not need to be array-entered formulas.
Agree with jhren, perhaps you could manually enter what you expecty the answers to be, and we can try and work with that?
On a side note, you need to remove the "" from around the 0...or just remove the 0...
=IFERROR(IF($B2=0,"",(INDEX($C$2:$C$10001, MATCH(MIN(ABS($C$2:$C$10001-$B2)), ABS($C$2:$C$10001-$B2), 0)))),"")
=IFERROR(IF($B2="","",(INDEX($C$2:$C$10001, MATCH(MIN(ABS($C$2:$C$10001-$B2)), ABS($C$2:$C$10001-$B2), 0)))),"") (prefered)
By using "0", you are forcing a check for a text 0, not a numeric 0
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi Jhren thanks for the reply. Let me try to explain. Let's only look at columns B, C, D and H
We send out an email to our security guards which is time stamped in column B (CHECK IN SENT TIME(0)) to the address in H ("Sent To")
The Security Guard replies and it is timestamped in Column C (Check In Received Time(G).
I would like Column D to give me the best match between Column B and C with the same value of Column H.
So in theory if a guard skips the step of replying the sheet will give me the last known response (Column C) in comparison to the most recent Check In Sent Time (Column B).
Does this make sense? I think I need to do this with Index,Match with column H and Column B being the anchors.
Thanks again for your help. Let me know if I can explain it more.
Column B is the
Try...
...in D2, and copy down.
Please Login or Register to view this content.
Last edited by jhren; 05-15-2015 at 10:53 PM.
jhren that worked, but I realized the data I was pulling wasn't enough info for what I wanted to accomplish. I needed to have the Sent to (time) & Sent to (email address) as well as the Received (time) and Received from (email address) to be able to have a unique identifier. I have attached a revised sheet. Ideally if the guard does not respond to the email I would like the sheet to lable the "Adjusted Received Time as NA. Thank you for all your help.
In D2 and copied down...
...or...Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by jhren; 05-16-2015 at 11:23 AM.
It is getting closer. Let me start with saying I REALLY appreciate your help. I have been stuck on this for awhile now and it is the final piece of the very large puzzle. OK so I have attached a revised sheet with some clearer information that may make better sense to you. Do you think this can be done or am I nuts for thinking this can work?
See attached file.
I can get close, but there's some conflict in your logic vs what I understand it to be (red) and some outright conflicts as I see them (yellow).
Changed formulas in columns C and F (achieves same result, so not required, but it made better Excel sense to me)
Thank you jhren. I see how close we are. I have an idea (see attached) maybe it can be accomplished this way? Did I thank you for your help yet?
In D2, copied down...
Please Login or Register to view this content.
jhren you are a genius! It totally works. I am so relieved and so grateful. Thank you thank you!!!
You're quite welcome. Glad I could help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks