Thank you both.



ArviLaanemets, I started implementing your method in a very big excel workbook. I was approached by the requester to clarify further. Here is the clarification:

Everything remains the same what changed is that we are looking for individuals with H0004 and then getting the other codes within the same week (within 7 days). I think this will reduce the result tremendously.

I modified & attached a sample workbook.

TIA

Regards,

Attached Files

Sample1.xlsx (12.7 KB, 10 views)

From your workbook example, it looks more like you want to get all codes for ID when in week around/after of H0004 aren't any other codes, not codes for one week

Some details are missing for me!

Are you meaning week as calendary unit, or as 7-day time period?
When week is calendary unit, then do you use US calendary (the week starts with Sunday) or ISO Calendary (the week starts with Monday)?
When week is 7-day time period, then does it apply to dates before and after H0004 (13 days total), or only for dates after H0004 (7 days total)?
Must all conditions apply for all H0004 codes for same ID, or only for latest one (e.g. what happens when in year 2018 was H0004 with another code in next day, but in year 2019 was H0004 without any other code less than 7 days apart)?
How must rows with no dates handled?

ArviLaanemets, thanks.

Correct, the goal is to identify if a given ID/individual has H0004 and then gets any other codes within 7 days intervals.
e.g. individual gets H0004 code on 5/1/20 and then gets G0400 on 5/6/20, then the way it’s done manually is to get the difference i.e. 6-1= 5 days. Then we flag this as it is <= 7 days and not allowed.

But, say if individual gets H0004 on 4/7/19 and gets G0401 on 4/18/19 then it’s allowed (18-7=11 days) over 7 days intervals and we do not need to flag this ID/individual.
7-day time period
Only, dates after H0004 (7 days total)
To clarify & simplify things, let’s say we have 4 codes:
H0004 (main code)
G0400
G0401
80008
Simply put, H0004 code cannot be followed or combined with any of the other 3 codes within 7 days. But, H0004 code by itself can be provided daily or at any time (no restrictions)

In your example,
a. if in year 2018 H0004 with another code the next day= not allowed
b. in year 2019 H0004 without any other code less than 7 day apart = allowed
Usually, the date field is almost always populated