Search code examples
excelduplicatesexcel-2013

How do I find all instances of related to a value in a another cell- EXCEL 2013

I'm stuck with the captioned and I'm not sure how to proceed.

Problem statement: I have 2 columns. Column A contains names that may repeat. Column B contains dates (DD-MMM-YY). Now I need to highlight/find/mark all cases where a particular name in column A has 2 dates a week/7days apart in column B.

Illustration:

Example of data that needs to be marked

In the above image only Jack and Genea should be highlighted/found/marked as they occur more than once and have dates in column B that are a week apart.

Could this be solved by using Formulas or VBA? Any leads, hints, tips, illustrations would be greatly appreciated.

Solution

  • Depending on clarifications in comments, there are potentially 4 different answers here. If the requirement is to highlight rows for the same Person who are exactly 7 days apart, regardless of whether there is another entry that fits between them (e.g. Sunday - Wednesday - Sunday), then you can just use this:

    =(COUNTIFS(A:A, A1, B:B, B1+7) + COUNTIFS(A:A, A1, B:B, B1-7))>1

    This will check for any rows where the Name matches the current {Row Name}, and the Date is exactly the current {Row Date} ± 7

    If you can't have any dates between the rows, then it gets slightly more complicated. If you have Office 365 or Office 2019, then you can use the MINIFS and MAXIFS functions to find the next date up or down

    =OR(MINIFS(B:B, A:A, A1, B:B, ">" & B1)=(B1+7), MAXIFS(B:B, A:A, A1, B:B, "<" & B1)=(B1-7))

    If you have an older version of Office, you will need to replace MINIFS and MAXIFS with an Array Formula:

    =OR(SUMPRODUCT(MIN(B:B+9999*(1-(A:A=A1)*--(B:B>B1))))=B1+7, SUMPRODUCT(MAX(B:B*--(A:A=A1)*--(B:B<B1)))=B1-7)

    (You'll notice that the ability to "zero out" the values makes our MAXIFS substitute simpler than our MINIFS one, where we are adding 9999 as our discard method)

    For the last 2 cases, where you want to highlight ALL rows for an individual when either of those other conditions is matched for any of their rows, then you need a far more complicated Array Formula.