Flag several duplicate columns that occur within a 3 day range.

Wrush4

New Member
Joined
Oct 6, 2015
Messages
4
Hi,
I have the following named Ranges
Activity ID Numeric, Unique --Key Field
Customer Name Name
Customer ID Numeric,
Ticket ID Numeric
ClosedDate1 Date
AgentID1 FLast
SCIP Event Yes / No

What I am attempting to do, is Flag SCIP Event With a "Yes" for each activity ID where the Customer ID has a duplicate with a higher Activity ID and occurs within 3 days.
I currently have the following, which works, but requires an advanced sort of Customer ID and Ticket ID on the raw data page (20k rows currently) to work properly, this has to be redone every time the raw data is updated (daily)

=IFERROR(IF(AND(COUNTIFS(CustomerID,[@[Customer ID]])>1,OFFSET([@ClosedDate1],1,0,1,1)-[@ClosedDate1]<4,OFFSET([@[Customer ID]],1,0,1,1)=CustomerID),"Yes","No"),"No")

I initially tried using an If(And(Index(Match)))), which obviously failed due to match only going to the first record. I would prefer to do this without VBA, but any working solutions would be appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Activity ID Customer ID ClosedDate1 SCIP Event
175380113 66390 1/18/2016 No
175108970 67325 1/3/2016 No
175377969 67527 1/18/2016 No
175329009 69907 1/15/2016 No
175254667 70434 1/11/2016 Yes
175288212 70434 1/13/2016 Yes
175289338 70434 1/13/2016 No
175350127 71084 1/16/2016 No
175436058 71084 1/21/2016 No
175419568 72426 1/20/2016 No
175477439 73218 1/23/2016 No
175269048 73505 1/12/2016 No
175500262 74267 1/24/2016 No
175220443 74441 1/9/2016 No
175398826 74561 1/19/2016 No
175477283 75164 1/23/2016 No
175235463 75843 1/10/2016 No
175311663 75843 1/14/2016 No
175479183 75843 1/23/2016 No
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top