Need IIf statement to determine Member Eligibility please

[LukeBourke]

I understand man.



Let me put a sample database together for you (something real simple) so that way you can play with the data yourself. Sorry for my confusion....

I can't promise when, though, because I have other projects that need to be done quickly that might take a number of days...

But hey, can I ask you for a formula in Excel 2010:

This would be a lot easier than my current Access query question??

[June7]

Ask anything you want, I might even have an answer.

[LukeBourke]

So I am providing some sample data for this.

What I need an Excel function to do is "if the Proc Type and the Proc Code are the same, take the Unit Value, Basic Rate, Child Rate, and ER Rate values of only the records having "O" as the Proc Type and insert those values into the 2nd set (Unit Value, Basic Rate, Child Rate, and ER Rate) of the records having anything not "O" (like K and P, for example).

The purpose of this is once I have all of the cells filled in, I will delete the records having "O" as their Proc Type (and we will use the Unit Value, Basic Rate, Child Rate, and ER Rate of the newly inserted "O" values that are inserted into the 2nd set (Unit Value, Basic Rate, Child Rate, and ER Rate) in the records having anything not "O" that match the Proc Type and Proc Code.
I am not worried about the UA OP and UB OP columns as of yet.

Notice I highlighted records having "O" in Proc Type that I took the values from and records not having "O" in Proc Type that I inserted values into the 2nd set (Unit Value, Basic Rate, Child Rate, and ER Rate): I am not sure if I can do Conditional Formatting for that (not a big deal if I have to manually highlight the records affected).

[June7]

You said "if the Proc Type and the Proc Code are the same" but by the examples in spreadsheet the Proc Type are not the same when Proc Code are - is there a "NOT" missing from that statement? Also, all but 2 of the records are 'highlighted' in red. Those 2 records are the only ones that don't pair with another record having same Proc Code. However, think I get it.

If this were a table in Access, could easily accomplish with queries.

Try this:

1. sort rows by Proc Code ascending and Proc Type descending

2. formula in cell V1 and copy down: =IF(AND(B1=B2, A1<>A2), TRUE, FALSE)

3. formula in cell P1 and copy down then across: =IF($V2,D1,"")

If that doesn't work, will probably need VBA code.

[LukeBourke]

Nope, that isn't working (the challenge here I believe is that the "O" Proc Code record having the same Proc Type as another record not like "O" Proc Code is that it could be above or below it, which makes it not work when you use a formula that only looks at current record with a comparison operator looking at only record 1 below. Maybe there is a way to check 1 above and 1 below??)

And this formula doesn't check if the Proc Code is "O" so it is working even when record "K" Proc Type is directly above record "P" Proc Type and they happen to have same Proc Code.

So I changed V column formula to:

=IF(A292="O",IF(OR(AND(A292<>A291,B292=B291),AND(A 292<>A293,B292=B293),AND(A292<>A290,B292=B290),AND (A292<>A294,B292=B294)),TRUE,FALSE))

Note there could be 2 above the "O" Proc Type records not like "O" but having same Proc Codes (see screenshot)



I was looking at this link: http://www.bluemoosetech.com/microso...ons.php?jid=19&

But where to go from there? The P column formula is not extracting the "O" record currency values and putting them in the correct records??

So I modified that P column formula to work like this:

=IF(OR(AND($V297=FALSE,$A298="O"),AND($V297=FALSE, $A296="O"),AND($V297=FALSE,$A299="O"),AND($V297=FA LSE,$A295="O")),$D298,"")

Problem is that last part of the IF statement that tells it to pull $D298,"" . I need it to pull the correct $D### depending on which records have not like Proc Types (1 of them being an "O" and the other not like "O" and the Proc Codes being the same)???

I was trying something like this but it threw an error at me:

=IF(OR(AND($V297=FALSE,$A298="O"),AND($V297=FALSE, $A296="O"),AND($V297=FALSE,$A299="O"),AND($V297=FA LSE,$A295="O")),OR($A298="O",$A296="O",$A299="O",$ A295="O") $D298,"")

Same thing here:

=IF(OR(AND($V297=FALSE,$A298="O"),AND($V297=FALSE, $A296="O"),AND($V297=FALSE,$A299="O"),AND($V297=FA LSE,$A295="O")),IF($A298="O",$D298,""),IF($A296="O ",$D298,""),IF($A299="O",$D298,""),IF($A295="O",$D 298,""))

[June7]

What I suggested works with the sample data provided, none of which has Proc Type of P. So that was not representative data?

[LukeBourke]

You are missing the point...

Whether you change the Proc Type to P or Z or K or whatever, the point is if A2<>A3,B2=B3.....etc, etc.

I came up with some good formulas I think, but can't seem to make it work for all scenarios (just need a little help with the formulas).
If you copy and paste the formulas (and of course, change the cell reference numbers for the small number of records in my sample file, you can see what happens....

I was playing around with another formula, but it didn't work (but maybe you can see what I am thinking might work or what I am getting at here):

=OR(IF(AND(A4<>A5,B4=B5),C5,""),IF(AND(A4<>A6,B4=B 6),C6,""),IF(AND(A4<>A3,B4=B3),C3,""),IF(AND(A4<>A 2,B4=B2),C2,""))

Or maybe just using IF statements (but that doesn't work either)....

=(IF(AND(A4<>A5,B4=B5),C5,""),IF(AND(A4<>A6,B4=B6) ,C6,""),IF(AND(A4<>A3,B4=B3),C3,""),IF(AND(A4<>A2, B4=B2),C2,""))

What am I doing wrong with this formula or my previous posts formula(s)??

I think I have got it (check out the attachment--simple example!!) (Of course, I would have to extend the nested IF statement to include 2 rows below and 2 rows above).

[June7]

I get the point. My point was I did not have representative sample data to test with. If I had I would have noticed those conditions and worked with them. I will try to take another look this weekend.

[LukeBourke]

I actually have a macro now that will do the job I think way better than a formula can do because of the Cell References sometimes will pick up records not like "O".

So after I test it thoroughly, I will post it up here so you can tell me what you think (I don't know VBA, but want to learn as much as I can)....

[LukeBourke]

Thank you in advance for all you do.

Attached is a database sample.

I think I would prefer to have the Eligibility column say Eligible if any of the DATEFROM (Date of Service) dates for a particular member are truly Eligible from any of his/her other Member Eligibility Histories\Sequences (so that way when I export it to Excel, I could just filter out the "Not Eligible" list and that will be our list to RECOVER payments made to Providers).

I hope this helps and let me know of any questions.

[June7]

This is solved?

[LukeBourke]

No, it is not solved as of yet. Hence, the reason why I posted that sample database with the same query structure and Member Eligibility IIf statement.

If you can recall, the problem here is that the same Member's record will show up however many times they have Eligibility Histories\Sequences with the DATEFROM (Date of Service) being the date to see if the Member was "Eligible" or "Not Eligible". If the DATEFROM is >=OPFROMDT and <=OPTHRUDT, then that Member would be Eligible to receive whatever service they received. Problem is that the DATEFROM repeats because of the fact that the MEMB_HPHISTS table stores Member Eligibility Histories\Sequences which appear on query results.

You can see by running the query in my sample database where a member will have a truly "Eligible" DATEFROM that falls between the OPFROMDT and OPTHRUDT, but then more records will appear for that same member and show "Not Eligible" because they were truly "Not Eligible" by the other Histor(ies)\Sequence(s).

What I need is, if same Member and same DATEFROM, but different OPFROMDT and different OPTHRUDT, make all Eligibility column say "Eligible" (kind of a way to trick the query).

Reason being is that once I get the results, I plan to export to spreadsheet and filter for only "Not Eligible" so that RECOVERY department can send mail to Providers requesting RECOVERY PAYMENT for claims we shouldn't have paid........I hope that all makes sense to you?

[June7]

You want to show eligibility for each record in CLAIM_MASTERS?

Consider this query:

SELECT dbo_RVS_CLAIM_MASTERS.MEMB_KEYID, dbo_RVS_CLAIM_MASTERS.DATEFROM, dbo_RVS_MEMB_HPHISTS.OPFROMDT, dbo_RVS_MEMB_HPHISTS.OPTHRUDT, [DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()) AS Eligibility
FROM dbo_RVS_MEMB_HPHISTS RIGHT JOIN dbo_RVS_CLAIM_MASTERS ON dbo_RVS_MEMB_HPHISTS.MEMB_KEYID = dbo_RVS_CLAIM_MASTERS.MEMB_KEYID
WHERE ((([DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()))=True));

With the data provided, all 3 members show Eligibility True. Is this what you would expect? The data results in only one record per member. I expect situation could arise that would cause several records for each member to return true. So Consider this version:
SELECT dbo_RVS_CLAIM_MASTERS.MEMB_KEYID, [DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()) AS Eligibility
FROM dbo_RVS_MEMB_HPHISTS RIGHT JOIN dbo_RVS_CLAIM_MASTERS ON dbo_RVS_MEMB_HPHISTS.MEMB_KEYID = dbo_RVS_CLAIM_MASTERS.MEMB_KEYID
GROUP BY dbo_RVS_CLAIM_MASTERS.MEMB_KEYID, [DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date())
HAVING ((([DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()))=True));

Now join one of those queries to CLAIM_MASTERS table.

[LukeBourke]

So I chose your 2nd SQL syntax since I believe we are talking about the same situation when same member could have several records (because different DATEFROM (Dates of Services) dates). Actually, the same member should show multiple records for the same DATEFROM with different OPFROMDT dates and different OPTHRUDT dates showing their Eligibility History (only difference between the problem and the solution would be the Eligibility column showing Eligible, not True (or False) if the DATEFROM is >=OPFROMDT And <=OPTHRUDT (Between isn't correct here because a member is Eligible on the first and last days of Eligibility, too) (that is greater than or equal to any of the OPFROMDT dates And less than or equal to any of the OPTHRUDT dates).

I am attaching my sample DB again to show you what I tried (and what I think you mean to do). The Eligibility column shows all as "-1" ??

[June7]

BETWEEN AND is inclusive for the range begin and end values so is equivalent to using >= and <=.