Excel Remove Duplicates based on time condition

Question

A 3 month rolling time condition is the basis for my search for duplicates to be eliminated from a 250,000-row Excel sheet.

We have a lot of user IDs and the dates on which they visited, however many of these visits are spread out over long periods of time (often more than a year), and many of them occur on the same day or a few days.

An example is the greatest method to illustrate what I want to do. So if they came to visit on January 1, January 1, January 3, January 8, February 4, June 5, December 1, and December 2, I would want to use the first date of January 1, June 4, and December 1.

If they came on January 1, January 2, January 3, February 8, or April 9, then on August 1 or September 1, I would like January 1 and August 8.

As a result, we want to book the initial date, then observe how frequently they return within 3 months of each visit, and if they go more than 3 months without coming back, book the first date they have after they do. After three months, they occasionally return four or five more times, and the data may cover several years.

Is there a way I can accomplish this?

Answer 1

Try this (description below) if the UserID is in column A, the VisitDate is in column B, the headings are in row 1, there is a blank row in row 2, and the data begins in row 3:

Array Formula version:

• sort the rows ascending by VisitDate
• in B2 put 1/1/1900 so it won't match anything (but it has to be a date)
• in C3 put this array formula (press control-shift-enter instead of just enter):
  • =SUM((B$2:B2<DATE(YEAR(B3),MONTH(B3)-3,DAY(B3)))*(A$2:A2=A3))=SUM((A$2:A2=A3)*1)
• Copy the formula in C3 down to every row of data
• Filter on Unique = TRUE
• if you want to resort you will need to copy and paste back column C by values

New non-array formula version:

• sort the rows ascending by VisitDate
• in B2 put 1/1/1900 so it won't match anything (but it has to be a date)
• in C3 put this normal formula (just press enter):
  • =COUNTIFS(B$2:B2,"<"&DATE(YEAR(B3),MONTH(B3)-3,DAY(B3)),A$2:A2,A3)=COUNTIF(A$2:A2,A3)
• Copy the formula in C3 down to every row of data
• Filter on Unique = TRUE
• if you want to resort you will need to copy and paste back column C by values

This produces the following with my sample data (array formulas may take a very long time to calculate for lots of rows):

   |    A   |      B     |   C
---+--------+------------+--------
 1 | UserID |  VisitDate | Unique
 2 |        |  1/01/1900 | 
 3 | a      |  1/01/2017 | TRUE
 4 | a      |  1/01/2017 | FALSE
 5 | b      |  2/01/2017 | TRUE
 6 | b      |  2/01/2017 | FALSE
 7 | a      |  3/01/2017 | FALSE
 8 | c      |  3/01/2017 | TRUE
 9 | c      |  3/01/2017 | FALSE
10 | b      |  4/01/2017 | FALSE
11 | c      |  5/01/2017 | FALSE
12 | a      |  8/02/2017 | FALSE
13 | b      |  9/02/2017 | FALSE
14 | c      | 10/02/2017 | FALSE
15 | a      |  4/06/2017 | TRUE
16 | a      |  5/06/2017 | FALSE
17 | b      |  5/06/2017 | TRUE
18 | b      |  6/06/2017 | FALSE
19 | c      |  6/06/2017 | TRUE
20 | c      |  7/06/2017 | FALSE
21 | a      |  1/12/2017 | TRUE
22 | a      |  1/12/2017 | FALSE
23 | a      |  2/12/2017 | FALSE
24 | b      |  2/12/2017 | TRUE
25 | b      |  2/12/2017 | FALSE
26 | b      |  3/12/2017 | FALSE
27 | c      |  3/12/2017 | TRUE
28 | c      |  3/12/2017 | FALSE
29 | c      |  4/12/2017 | FALSE

I hope this helps you.