I am trying to create something that will look for duplicate account numbers and when found will compare the dates for each of occurrence, checking to see if the dates are within 30 days of each other. If they are within 30 days of each other I would like it to flag the repeated occurrence (the second, third, fourth) with "repeat" but not the first occurrence of that service order.
The data starts on row 10.
The column that contains the Service Account number is I
The column that contains the date information is Y
I would like the "flag" in column AW
Data is added to this table every month and the ability to dynamically check for repeats is very important.
As an example of the data and the hoped for result (without the information in parenthesis)
<tbody>
</tbody>
I'm not sure if I'll need a macro or a function. I haven't tried to write a macro for this (I am a vba noob)
The data starts on row 10.
The column that contains the Service Account number is I
The column that contains the date information is Y
I would like the "flag" in column AW
Data is added to this table every month and the ability to dynamically check for repeats is very important.
As an example of the data and the hoped for result (without the information in parenthesis)
Service Account | Date | Repeat Flag |
236609 | 01/2/13 | no |
239723 | 01/2/13 | no |
239724 | 01/13/13 | no |
236609 | 01/20/13 | Yes (01/20/13 within 30 days of 01/2/13) |
239724 | 01/21/13 | yes (01/21/13 within 30 days of 01/13/13) |
250076 | 02/15/13 | no |
236609 | 02/18/13 | yes(02/25/13 within 30 days of 01/20/13) |
240405 | 03/2/13 | no |
225879 | 03/5/13 | no |
232568 | 03/6/13 | no |
239725 | 03/8/13 | no |
123154 | 03/8/13 | no |
236609 | 03/10/13 | yes(03/10/13 within 30 days of 02/18/13) |
239724 | 03/11/13 | no (last occurrence more than 30 days) |
<tbody>
</tbody>
I'm not sure if I'll need a macro or a function. I haven't tried to write a macro for this (I am a vba noob)