CODEWITHSUNDEEP
excelexcel-formula
mattinwpg
mattinwpg

Reputation: 163

Find repeat names in column B based on date criteria in column A

I have two columns of data in Excel. Column A is an interaction date, and column B is a name. I'm looking to devise a way to identify repeat contacts by the same name that occur within 1 day of each other. eg:

Date           Name
2016/01/01     John Wayne
2016/01/01     Paul Friesen
2016/01/01     John Wayne
2016/01/01     Alex Roschenko
2016/01/02     Paul Friesen
2016/01/02     Peter Mansbridge
2016/01/02     Jake Snake
2016/01/03     Paul Feig
2016/01/03     John Wayne

Using only this sample data, the result I would be looking for is 2 (John Wayne and Paul Friesen both repeated in =< 1 day.

I'm not sure if Excel or Access are the better tool to use for this, but I'm more experience in Excel, I just haven't been able to come up with a formula...

Upvotes: 0

Views: 56

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

Use a helper column.

In a blank column put the following in row 2:

=SUM(COUNTIFS(B:B,B2,A:A,A2+{-1,0,1}))>1

This will return a column of TRUE/FALSE

Then referencing the helper column we use the following array formula:

=SUM(IF($C$2:$C$10,1/COUNTIFS($B$2:$B$10,$B$2:$B$10,$C$2:$C$10,TRUE)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter. If done properly then Excel will put {} around the formula.

This formula counts the unique names that have TRUE in the helper column. So no matter how many times the name has TRUE next to it, it will only be counted once.

enter image description here

Upvotes: 2

Related Questions