Formula that determines multiple occurances within a pre-determined time period

[wheddingsjr]

Good morning all



I have a excel spreadsheet with over 9k lines in it. Rather than going through each line individually, I was hoping someone could tell me how I could populate a field based on the attached example. The example shows four columns and what i would like to do is determine when a member has multiple professional "TYPES" that occurred within a year of each other. The formula would populate the "RETRACT" field with "Y" if a member has more than 1 professional type within a year. If the member has a "Institutional" AND Professional type, the retract field would remain empty. Any help would be appreciated.

Thanks

[Pecoflyer]

Hi and welcome
could you please post a sample sheet with some data and desired results ( Click - Go advanced - manage attachments and folow the wizard)
Thanks

[wheddingsjr]

Thanks for the response Pecoflyer. I have already attached an example.

[wheddingsjr]

If for some reason there was no attachment, which I thoought I attached I will attach now.

[NBVC]

Does this work?

=IF(AND(C3="Professional",C2="Professional",A3=A2, B3-B2<=365),"Y","")

[wheddingsjr]

Hi NBVC

That is almost perfect except that there are times when there are more than 2 lines per MEMNO (see highlighted in the attached). I guess I could go through the list after using the formula to find those MEMNO's that have more than two. I should have said the 365 days start from the first occurance, than after the 365, the whole process starts over again. Make sense?

[NBVC]

I am not sure I understand.

Why exactly should I not have a Y in the last cell?

Will there be occasions of 4 MEMNO's? Then what?

[wheddingsjr]

Good morning NBVC

The reason why the third line would not have a Y is because it is over a year since the first occurance. The second occurance is within a year, but when you look at the dates, the third one is more than a year. Based on review theres really not that many so I can just go through the list and manually correct those using your original formula which as I said previously worked like a charm.

Thanks

[wheddingsjr]

Just for clarification, once the year span is met, then the 365 days starts again from the last date. I hope that makes sence because its difficult for me to out in words even thoguh I know what I am trying to convey. Either way, I used your formula and it was perfect...I scrolled through the lines to fix those occassional hiccups but other than that your formula worked and I appreciate your assistance.