excelindexingnestedmatchifs

Identifying repeat tickets in Excel with ifs, index, match, nested if, countifs

I am having fits with an excel formula I just cant figure out and sometimes more than one set of eyes can make short work of a problem. I have been chasing google results for many hours on this, so here I am finally begging for help.

One workbook with two tabs. Tab "import_1" contains imported data and is simply the account number (column A, accountNum), and the date the ticket was created (column B, Date_Created). Tab "import_2" contains imported data as well and is simply the account number (column A, accountNum), the date the second ticket was created (column B, Date_Created) and then the helper column that contains the unknown solution to my question (column C, Repeat_?).

Tab name "import_1":

accountNum   | Date_Created
-------------|-------------
10000001     | 24-APR-2023 12:04:00 AM
10000002     | 25-APR-2023 12:04:00 AM
10000003     | 25-APR-2023 12:04:00 AM
10000004     | 25-APR-2023 12:04:00 AM
10000005     | 25-APR-2023 12:04:00 AM

Tab name "import_2":

accountNum   | Date_Created           | REPEAT_?
-------------|------------------------|----
10000001     | 04/23/2023 12:01:00 AM | No
10000001     | 04/24/2023 12:01:00 AM | No
10000001     | 04/24/2023 12:04:00 AM | Yes
10000001     | 04/24/2023 10:41:00 PM | Yes
10000005     | 04/29/2023 12:10:00 AM | Yes

The question is that I need to identify REPEAT tickets that are created by accountNum. The rules for what is a repeat are simple;

  1. accountNum on tab "import_2" has to also be found on tab "import_1"
  2. those accountNum's from tab "import_2" that are actually found on tab "import_1" then need to have thier date/time stamped at the same date/time or after from tab "import_1". If those two conditions are met then we have a 'Yes', if not then a 'No'. Yes obviously means it is a repeat ticket because it was created for the same account after the first ticket was created and no means it is not a repeat because the second ticket was created before the first ticket was.

Helper columns can be added if it will get us to a solution, but some sort of fancy formula in tab "import_2" column C would be the best result. I have tried: if, ifs, countif, countifs, nested if, index/match and I cannot get any to work. Some of those work fine for the first part in identifying the duplicate account numbers but when I try to include the equal to or after (for Date_Created) is where it all goes to crud. Your expertise and help would be greatly appreciated.

EDIT: Tab 'import_2" cell C3 corrected to No

Solution

  • You need more than a "fancy formula" here.

    It seems you have neither fully, nor carefully, specified the conditions for whether a ticket is a repeat. The 2 conditions you've spelled out would indicate that the 2nd ticket on import_2 is NOT a repeat.

    10000001 | 04/24/2023 12:01:00 AM | Yes

    That's because it does not repeat a ticket on import_1, as your specified conditions require. But your sample declares that it is a repeat, presumably because it repeats the ticket above it on import_2.

    If my interpretation is correct, you need a condition that tests whether a ticket on import_2 duplicates another ticket on import_2, in addition to the two conditions you've provided. This gets complicated because it requires mixing both AND and OR conditions. Excel's COUNTIFS() statement doesn't allow OR conditions.

    One possibility is using two COUNTIFS(), one for each tab, to handle the OR. I've kept them in separate columns to help keep it clear. The first column checks for a repeat of a ticket on import_1, the second does the same test on import_2.

    D2:  =COUNTIFS(import_1!$A$2:$A$6, import_2!A2, import_1!$C$2:$C$6, "<" & import_2!C2)

    E2:  =COUNTIFS(import_2!$A$2:$A$6, import_2!A2, import_2!$C$2:$C$6, "<" & import_2!C2)

    The REPEAT? column sums those two formulas; if SUM > 0 then you have a repeat:

    F2:  =IF(D2 + E2 > 0, "Yes", "No")

    I've converted both datetime columns to values (Column C on both tabs) just to avoid any issues with Excel seeing them as text instead of datetimes:

    C2:  =DATEVALUE(B2) + TIMEVALUE(B2)

    That might be why it "all goes to crud" when you compare the dates. Another point is that your condition 2 says created AFTER the first ticket, but your text says "equal to or after" (that's why I said you haven't "carefully" specified the conditions). My formulas go with AFTER, per your stated conditions.

    And as @cybernetic_nomad said, it's always good to spell out what you've already tried.

    Tab import_2:

    Tab import_2