A formula to identify a repeat ID/Contact

[manny88]

I need to create a FCR report (First Call Resolution). This is when a person calls a office with a query and it is resolved during the first correspondence. I will then use a pivot tale to monitor the FCR levels in office daily, weekly etc.

On the attached I have included a example report table. I need a formula in the colom where it says "Repeat" with a condition to identify 1. the Call ID and 2. the Customer Name. If one or the other match within a 72 hour period then it should be counted as a repeat call (unresolved if customer calls back within 3 days -- If it is concerning a different query, this can be changed manually) and display 0, otherwise if it is resolved display 1

I tried =IF(AND(C2=C2,D2=D2),0,1) but it didn't work

I want to be able to paste in data in the coloms before the "Repeat" colom in future where I need the formula to identify return the figures 0 and 1

[JeteMc]

Paste the following formula into G2 and double click the fill handle to copy down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[manny88]

Paste the following formula into G2 and double click the fill handle to copy down:

Please Login or Register  to view this content.

Let us know if you have any questions.

That works great, I forgot to mention the date being taken into consideration so another thanks on that!

I will put it to practice at work. I may need to alter this formula, because if the same customer calls back within 3 days about a CS General query, and the previous was about Billing - this will not be classed as a repeat call to avoid doing it manually in future -- Could this be possible?

[JeteMc]

I may need to alter this formula, because if the same customer calls back within 3 days about a CS General query, and the previous was about Billing - this will not be classed as a repeat call to avoid doing it manually in future

Try this:

Please Login or Register  to view this content.

Let us know if you have any questions.

[manny88]

Try this:

Please Login or Register  to view this content.

Let us know if you have any questions.

That works perfect actually. I will put this into practice at work and see if it works with the system data. It's a bigger table, whereas in my example I only included the relevant coloms I would look to use.

Thanks a lot!

[manny88]

Try this:

Please Login or Register  to view this content.

Let us know if you have any questions.

Actually, would you mind explaining the formula to me so I can understand it in case I have to adjust it at work to a different colom or condition?

Thanks

[JeteMc]

If you run Evaluate Formula from the Formula tab while one of the cells in column G is selected you will see that the formula is going to make true/false arrays from (C$2:C$100=C2) etc. Since those arrays are either multiplied by or added to one another they in turn form arrays of 1's and 0's. SUMPRODUCT will add those 1's and 0's together and then, after subtracting two*, the IF function will evaluate whether or not the result is TRUE, any sum other than zero, or FALSE, zero. As to the individual arguments in the SUMPRODUCT function: (C$2:C$100=C2) will compare the value of C2 to the values in column C. It will of course find one match, C2=C2 and mark that as TRUE along with any other instances where to ID is 55511 the rest will return a value of FALSE. Since the desired result is to find if the ID and Queue are the same the multiplication symbol is used between (C$2:C$100=C2)*(E$2:E$100=E2), which can be thought of as being the word 'AND'. The desired result also states that if either the ID/Queue or the NAME/Queue match, so the addition symbol is used between (C$2:C$100=C2)*(E$2:E$100=E2)+(D$2:D$100=D2)*(E$2:E$100=E2), which can be thought of as being the word 'OR'. The results of the IF function are given as 1 and 0 based on the requirement made in post #1, the two values could have also been yes/no, resolved/unresolved etc. I hope that this makes some sense, let us know if you have any questions.
*Two is subtracted because in each row the pairs Cn&En and Dn&En will match themselves.
If the formula has been explained, and since it works for the situation, please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[nraec]

Hello, I was looking for a similar solution, however, when I tried to add more rows with dates exceeding 72 hours,
it is not resulting in what I expected--the formula is still recognizing data over 72hrs as a repeat.

[6StringJazzer]

Hello, I was looking for a similar solution

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread