Apr 22 2018
12:48 AM
- last edited on
Jul 25 2018
11:47 AM
by
TechCommunityAP
Apr 22 2018
12:48 AM
- last edited on
Jul 25 2018
11:47 AM
by
TechCommunityAP
I recently faced a really interesting, exciting and useful-to-others challenge, and it turned out that I need help from someone better than me! :) a real MASTER!
Let's say an Excel workbook which has 2 sheets : "Sent" and "Inbox" contains data which is coming from an Exchange Mailbox(thats right! I'm an Exchange Admin) . the Idea is to calculate response time for a received Email that is replied! sheet's layout are as bellow :
Attributes.ConversationTopic | Folder Path | Subject | Sender.Name | DateTimeReceived |
we got error | \Inbox\ | we got error | Behrouz Amiri | 4/18/2018 11:55 |
sapleIssue | \Inbox\ | RE: sapleIssue | Behrouz Amiri | 4/16/2018 16:51 |
test03 | \Inbox\ | test03 | Behrouz Amiri | 4/16/2018 14:31 |
sapleIssue | \Inbox\ | sapleIssue | Behrouz Amiri | 4/16/2018 14:31 |
test1 | \Inbox\ | test1 | Behrouz Amiri | 4/16/2018 14:30 |
Attributes.ConversationTopic | Folder Path | Subject | Sender.Name | DateTimeSent |
sapleIssue | \Sent Items\ | RE: sapleIssue | infra test | 4/18/2018 23:59 |
di nemune imale farsi | \Sent Items\ | di nemune imale farsi | infra test | 4/21/2018 16:40 |
test | \Sent Items\ | test | infra test | 4/21/2018 16:36 |
we got error | \Sent Items\ | RE: we got error | infra test | 4/18/2018 12:05 |
test03 | \Sent Items\ | RE: test03 | infra test | 4/16/2018 14:32 |
sapleIssue | \Sent Items\ | RE: sapleIssue | infra test | 4/16/2018 14:32 |
test1 | \Sent Items\ | RE: test1 | infra test | 4/16/2018 14:32 |
I have tried really hard, but I couldn't do this. I also started a thread HERE and got an answer, but since I simplified the question, the correct answer couldn't be applicable for this real challenge.
the only important fields are Attributes.ConversationTopic and DateTime[Sent/Received]
DateTime values are actually DateTime values that are formatted in "m/d/yyyy h:mm"
when an email comes, it will appear in the "Inbox" sheet automatically. when user replies it, it will appear in Sent sheet. the ConversationTopic column is the first "subject" of the received email. then when this conversation continues, it causes that field not to be unique anymore. so we need to get the value of the nearest bigger (minimum value which is bigger) "DateTimeSent" for any "Attributes.ConversationTopic" in the "Inbox" table, from the "Sent" Table.
A real sheet is attached for easier workaround.
Apr 22 2018 03:24 AM
Hi
I would suggest MINIFS().
=MINIFS(Sent!$E$2:$E$8,Sent!$A$2:$A$8,A2,Sent!$E$2:$E$8,">"&E2)
Apr 22 2018 04:08 AM
its not working, it draws a #Name error..
Any Idea?
Apr 22 2018 05:15 AM
That is not the formula I posted.
Apr 22 2018 07:32 AM - edited Apr 22 2018 11:30 AM
Hi Behrooz,
The formula in the previous conversation works just fine, but you forgot to change the column references in the formula, this is why you got a #VALUE! error.
This is the formula with the correct column references:
=IF(ISNUMBER(MATCH(A2,Sent!$A$2:$A$9,0)),
SUM(IF(MIN(ABS(E2-IF(A2=Sent!$A$2:$A$9,Sent!$E$2:$E$9)))=ABS(E2-IF(A2=Sent!$A$2:$A$9,Sent!$E$2:$E$9)),Sent!$E$2:$E$9)),0)
Please find it in the attached file
By the way, you got a #NAME? error when you tried Detlef's formula because MINIFS function is currently only available in the Excel 2016 with Office 365.
It seems that you don't have an Office 365 subscription.
However, MINIFS does not always give you the correct result, in your case, you need to a powerful array formula.
Regards
May 04 2018 11:20 PM
Dear Haytham Amairah,
Thank you for helping me out, AGAIN! :)
your sample file works just fine, except its returning 1 wrong time stamp.
please review in the attached file, I just highlighted it in red. I tried to figure it out, but your formula is too professional to be solved!
May 06 2018 09:32 AM - edited May 06 2018 09:39 AM
Hi Behrooz,
Sorry about that, but can you please explain why that result of the formula is wrong?
The value returned by the formula is the nearest minimum DateTimeSent value.
May 07 2018 07:20 AM
Hi, there.
Well, I'm surprised by the simplicity of the solution that I've ended up with (btw, I'm using Excel 2016):
=IF(
( MINIFS(( Sent!$E$2:$E$9 ),( Sent!$E$2:$E$9 ),( ">"&$E2 ),( Sent!$A$2:$A$9 ),( $A2 )) )=0,
"Not replied",
( MINIFS(( Sent!$E$2:$E$9 ),( Sent!$E$2:$E$9 ),( ">"&$E2 ),( Sent!$A$2:$A$9 ),( $A2 )) )
)
MINIFS returns the minimum of DateTimeSent WHERE DateTimeSent is greater than DateTimeReceived AND Inbox!Conversation = Sent!Conversation.
If the criteria excludes all records, MINIFS returns '0' so we can use that to indicate that a reply is yet to be sent.
Example file attached.
It includes a formula using INDEX/MATCH that incorporates MINIFS. the INDEX/MATCH approach is where I started for a solution, later realizing that MINIFS worked fine on it's own.
Hope that helps.
May 07 2018 08:04 AM
May 07 2018 08:04 AM
@behrooz amiri, I've just realized that my solution is the same as @Detlef Lewin's.
May 07 2018 09:52 AM
SolutionHi Behrooz,
In fact, I missed to consider this point that you mentioned (sorry about that!):
for each mail (conversation.Topic) dateTimeSent (ReplyTime) is bigger than DateTimeReceived (received mail time) which make sens!! :)
So please get rid of my previous formula, and use this one instead:
=IF(MIN(IF((Sent!$A$2:$A$9=A2)*(Sent!$E$2:$E$9>E2),Sent!$E$2:$E$9))=0,"",
MIN(IF((Sent!$A$2:$A$9=A2)*(Sent!$E$2:$E$9>E2),Sent!$E$2:$E$9)))
And please find it in the attached file.
Note: This formula is compatible with all versions of Excel, but if you decided in the future to get an Office 365 subscription, or to upgrade to the upcoming Office 2019, I advise you to replace it with the formulas suggested by @Detlef Lewin and @Deleted.
Their formulas are easier and don't require you to press Ctrl+Shift+Enter to get the correct result.
Regards
May 07 2018 10:34 AM
As a comment.
One more variant of that formula which doesn't require CSE is
=IF(AGGREGATE(15,6,1/(Sent!$A$2:$A$9=A2)/(Sent!$E$2:$E$9>E2)*Sent!$E$2:$E$9,1)=0,"", AGGREGATE(15,6,1/(Sent!$A$2:$A$9=A2)/(Sent!$E$2:$E$9>E2)*Sent!$E$2:$E$9,1) )
or simply
=IFERROR(AGGREGATE(15,6,1/(Sent!$A$2:$A$9=A2)/(Sent!$E$2:$E$9>E2)*Sent!$E$2:$E$9,1),"")
if apply custom format
yyyy-mm-dd hh:mm;@
to the resulting cells
May 07 2018 09:52 AM
SolutionHi Behrooz,
In fact, I missed to consider this point that you mentioned (sorry about that!):
for each mail (conversation.Topic) dateTimeSent (ReplyTime) is bigger than DateTimeReceived (received mail time) which make sens!! :)
So please get rid of my previous formula, and use this one instead:
=IF(MIN(IF((Sent!$A$2:$A$9=A2)*(Sent!$E$2:$E$9>E2),Sent!$E$2:$E$9))=0,"",
MIN(IF((Sent!$A$2:$A$9=A2)*(Sent!$E$2:$E$9>E2),Sent!$E$2:$E$9)))
And please find it in the attached file.
Note: This formula is compatible with all versions of Excel, but if you decided in the future to get an Office 365 subscription, or to upgrade to the upcoming Office 2019, I advise you to replace it with the formulas suggested by @Detlef Lewin and @Deleted.
Their formulas are easier and don't require you to press Ctrl+Shift+Enter to get the correct result.
Regards