Identify 7 day separation between product calls

[pivotguy]

I have a situation where the "same Rep" can not call the "same customer" and discuss "branded/unbranded" products within 7 days.
Please see the input file(text.xls) and expected output file. This is a subset(25 records) of the original file which have 100K records.

Any suggestion for a VBA Option

[SamT]

What about Rows 18 & 19? They have different account IDs.

If I understand you, the same Rep can call the same Account ID and discuss ABC Branded and XYZ Branded within 7 days.

If Row 7 happened on the 18th, would line 5 be included in Exceptions?

Just to be clear, IF today is the FEb 22, will Feb 29 be within 7 days?

Can different Reps call the Same Account and one talk about Branded and the other about unbranded within 7 days without triggering an Exception report?

[snb]

crossposted: http://www.excelguru.ca/forums/showthread.php?5567-Identify-7-day-separation-between-product-calls

[SamT]

Cross Posting (http://www.vbaexpress.com/forum/showthread.php?18537-Cross-Posting&p=137004&viewfull=1#post137004)

[pivotguy]

SAMT :


Q1. What about Rows 18 & 19? They have different account IDs.

pivotguy: You are correct Row# 18 and 19 have different account ID and should not be considered as exception.

Q2. If I understand you, the same Rep can call the same Account ID and discuss ABC Branded and XYZ Branded within 7 days.
Pivotguy: That’s correct. But not Branded and unbranded within 7 days


Q3. If Row 7 happened on the 18th, would line 5 be included in Exceptions?
Pivotguy : No Row#7 will not be included in exception.


Q4. Just to be clear, IF today is the FEb 22, will Feb 29 be within 7 days?
Pivotguy : 7 days including today. That means 22,23,24,25,26,27,28. The 29th considered as 8th day.


Q5. Can different Reps call the Same Account and one talk about Branded and the other about unbranded within 7 days without triggering an Exception report?
Pivotguy: That’s correct. In this case, the event will not triggered an exception.

[pivotguy]

I am new to these forum. Sorry for the cross posting. I do not know how to link different posting. Any help appreciable.

[SamT]

IF you are are not yet allowed to post links, delete the "http" form the link String

[SamT]

If you are not yet allowed to paste links, delete the "http" form the link address.

THe requirement to include all the information from all offending cells adds an order of magnitude to the complexity of the code.

Can you live with just Rep, the date of the first call, the date from the Row of the rest of the calls and the account #

Example
(Day 1) Column F: Call 1 = Rep, Date1, Account #
(Day 3) Column F: Call 2 = Rep, Date1, Date2, Account #
(Day 7) Column F: Call 3 = Rep, Date1, Date3, Account #
(Day12) Column F: Call 4 = Rep, Date3, Date4, Account #

[pivotguy]

What about the product which is an important attribute to determine exception?

[SamT]

If there is an exception, the assumption is that there are the two products.

Listing the one product from that row is not that hard, Listing all products from all Rows is what adds to the complexity.

The most basic algorithm requires iterating thru the rows with at least two cell references. One for the basic loop and the other to find the cells within 7 days. Call them Stepper1 and Stepper2. IF Stepper2 finds a match it can add the information from the Rows Stepper1 is at and the Row it is at. Stepper1 can add any information from the Row Stepper2 is at, so it would be not that hard to add all date and products to only the first Row that all Restrictions are found.

But, then Stepper2 has to move to the next Row and the information from the previous Restriction is lost.

When Stepper2 has moved to a row that is seven days after Stepper1, Stepper 1, must move down one Row and Stepper2 must start over just below Stepper1.

Imagine an Inch Worm that can stretch out seven days, but can only move one Row at a time. Restriction information can only be passed from one end of the Worm to the other.

Code Note: Run Stepper2 twice, Once to gather information, once to paste it? What about double info as in Day3 in my previous?

Let me cogitate on this.

Please post back to move this thread to my list of threads with new posts