Hello all
I have a spreadsheet with multiple columns and multiple rows. The idea is to determine if an “E&M” code was paid within 90 days of the “Intraocular” procedure for each MEMNO. The problem with the formula I tried (I tried an If/And formula), is that I cannot incorporate those “E&M” types that come BEFORE the “Intraocular” procedure. I am including a sample to show as an example of how I would like the formula to work. Any assistance would be greatly appreciated.
Thanks

Don't understand your table logic! You table will be have some logic when:
1. You need a formula in column 'WITHIN 90' returning "Y" when something described in table row is paid within 90 days from 'BEGDATE', "N" when it was paid later than 90 days from 'BEGDATE' or is not paid and currently more than 90 days is passed from 'BEGDATE', and returning an empty string when it is not paid and currently less than 90 days is passed from 'BEGDATE'. When this is the case, you need an additional field in your table (e.g. 'PayDate'), where date the payment was finished will be stored;
2. You enter into column 'WITHIN 90' manually "Y" or "N" when the payment is made. When this is the case, you need the information, is the entry within 90 days or not, to use right character. One possibility is to have an additional column with formula which displays "N" whenever the entry is out of 90 day limit and payment is not made yet (there is no entry in column 'WITHIN 90'). Or you use conditional formatting in your table to color entries out of 90 day limit and payment not made in one color, entries paid later than 90 days in another color, and entries paid earlier in 3rd color (no need for additional column then).

The formula for additional column in 2. will be something like

Hi Arvi

Thanks for your response, however, based on what you wrote, we may not be on the same page. (I may have not explained it correctly).The "MEMNO" and the "TYPE" columns are what should drive the formula. The idea is to determine for each "MEMNO" how long an "E&M" line appears AFTER a "INTRAOCULAR" line. If the "E&M" line appears BEFORE the "INTRAOCULAR" line per "MEMNO",, it is an automatic "N" because it happened before the "INTRAOCULAR". I need to determine if the "E&M" lines that appear AFTER "INRAOCULAR" lines are within 90 days of the "INTRAOCULAR" line for each MEMNO. I hope that clears it up. Or did I make it even more confusing?

Thanks

In case there never be 2 INTRAOCULAR entries for certain MEMNO with E&M entry between them, and in cases with several INTRAOCCULAR entries E&M entries before them must match only with 1st INTRAOCULAR entry, and E&M entries after them must match only with last INTRAOCULAR entry, it is possible to write a formula, but it is not trivial task.

Much easier (and allowing any order of entries) design will be the one, where every entry has separate column for INTRAOCULAR and R&M BEGDATE's.
I.e. columns like
MEMNO; IntraOcularDate; E&MDate; Within90

Thanks again Arvi

I figured it would be rather daunting. Since theres only 2,500 lines I was going to just got through them manually but thought I would at least see if there was a way I could do it via formula. I will try the seperate column suggestion to see if that works..once again thanks for the advise and help.

A macro approach option without adding any columns.

Attached Files

wheddeingsjr_Book1.xlsm (19.6 KB, 10 views)

Hi NoSparks

Did you add a macro? It doesnt appear to be. That is the result I am looking for but it looks like what I posted.

Alt + F8 keys together for macro dialogue