If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
If And Help?
Rick, the array you gave me is close, very close. When I entered it
in, these are the results I received: G L M S U Claim Code R. Code Paid Reason Code 2308 v7.23 3802 $49.99 2308 v7.23 3802 $29.99 D 2308 v7.23 3809 $33.00 D 2816 v7.23 3809 $96.00 D 2917 v8.07 5039 $57.35 D 2917 v8.07 5039 $93.18 2917 v8.07 5039 $63.07 Row 4 shouldn't have been flagged since R. Code is different from the previous 2 (3809 instead of 3802). Row 5 shouldn't have been flagged since Claim Number is different than others, not a duplicate. Row 8 should have been flagged since claim number, code, and R. Code are all the same and value is less than the $93.18. Lars: Your formula results in the below: G L M S U Claim Code R. Code Paid Reason Code 2308 v7.23 3802 $49.99 2308 v7.23 3802 $29.99 D 2308 v7.23 3809 $33.00 2816 v7.23 3809 $96.00 2917 v8.07 5039 $57.35 D 2917 v8.07 5039 $93.18 2917 v8.07 5039 $63.07 You guys sure have put me in the ballpark. I can do formulas, nothing as complex, or as good as you two. You guys have gotten me a heck of a lot closer than I ever would have. I'm going to try to look at your arrays and see if I can add too it (I'ld say slim and none are my chances of figure it out, but going to give it a shot If you guys have any other ideas, they are greatly appreciated! Thanks, Joe The one thing I noticed is this: On Aug 9, 12:26*pm, "Rick Rothstein \(MVP - VB\)" wrote: This array-entered** formula should do will do what you want (and it will work correctly evenifthe data is not sorted)... =IF(MIN(IF((G$2"")*(G$2:G$10000=G2)*(L$2:L$10000 =L2)*(M$2:M$10000=M2)=1,S$2:S$10000,""))=S2,"D","" ) ** commit the formula using Ctrl+Shift+Enter, not just Enter by itself The formula assumes your first data row is Row 2, place the formula in U2 and copy down. I set the formula to work with data down to Row 10000 maximum, butifyou know there is a maximum row less than 10000 below which there will never be data, you make the formula more efficient by changing the references with 10000 to that lower maximum row number. Rick wrote in message ... I redited since it messed up chart. *You don't have to worry about T, in this particular problem. G * * * * * * * L * * * * * * * *M * * * * * * S * * * * * * * *U Claim * * * *Code * * * *R. Code * * *Paid * * Reason Code 2308 * * * * *v7.23 * * * * 3802 * * * $49.99 2308 * * * * *v7.23 * * * * 3802 * * * $29.99 * * * * * *D 2308 * * * * *v7.23 * * * * 3809 * * * $33.00 2816 * * * * *v7.23 * * * * 3809 * * * $96.00 2917 * * * * * v8.07 * * * *5039 * * * $57.35 * * * * * * D 2917 * * * * * v8.07 * * * *5039 * * * $93.18 2917 * * * * * v8.07 * * * *5039 * * * $63.07 * * * * * * D |
#12
|
|||
|
|||
If And Help?
For Row 4, are you saying that a "unique" row (Claim, Code and R.Code match
no other rows) should not get flagged with a "D"? I figured a "unique" row, by definition, contains the lowest Paid price and should, therefore, be flagged with a "D"... is this not correct? I disagree with you on Rows 5 and 8. For Row 5, the Claim Number **IS** the same (I see 2917 for each of the last 3 rows in your example) and since it is, it should get the D and not Row 8. Rick wrote in message ... Rick, the array you gave me is close, very close. When I entered it in, these are the results I received: G L M S U Claim Code R. Code Paid Reason Code 2308 v7.23 3802 $49.99 2308 v7.23 3802 $29.99 D 2308 v7.23 3809 $33.00 D 2816 v7.23 3809 $96.00 D 2917 v8.07 5039 $57.35 D 2917 v8.07 5039 $93.18 2917 v8.07 5039 $63.07 Row 4 shouldn't have been flagged since R. Code is different from the previous 2 (3809 instead of 3802). Row 5 shouldn't have been flagged since Claim Number is different than others, not a duplicate. Row 8 should have been flagged since claim number, code, and R. Code are all the same and value is less than the $93.18. Lars: Your formula results in the below: G L M S U Claim Code R. Code Paid Reason Code 2308 v7.23 3802 $49.99 2308 v7.23 3802 $29.99 D 2308 v7.23 3809 $33.00 2816 v7.23 3809 $96.00 2917 v8.07 5039 $57.35 D 2917 v8.07 5039 $93.18 2917 v8.07 5039 $63.07 You guys sure have put me in the ballpark. I can do formulas, nothing as complex, or as good as you two. You guys have gotten me a heck of a lot closer than I ever would have. I'm going to try to look at your arrays and see if I can add too it (I'ld say slim and none are my chances of figure it out, but going to give it a shot If you guys have any other ideas, they are greatly appreciated! Thanks, Joe The one thing I noticed is this: On Aug 9, 12:26 pm, "Rick Rothstein \(MVP - VB\)" wrote: This array-entered** formula should do will do what you want (and it will work correctly evenifthe data is not sorted)... =IF(MIN(IF((G$2"")*(G$2:G$10000=G2)*(L$2:L$10000 =L2)*(M$2:M$10000=M2)=1,S$2:S$10000,""))=S2,"D","" ) ** commit the formula using Ctrl+Shift+Enter, not just Enter by itself The formula assumes your first data row is Row 2, place the formula in U2 and copy down. I set the formula to work with data down to Row 10000 maximum, butifyou know there is a maximum row less than 10000 below which there will never be data, you make the formula more efficient by changing the references with 10000 to that lower maximum row number. Rick wrote in message ... I redited since it messed up chart. You don't have to worry about T, in this particular problem. G L M S U Claim Code R. Code Paid Reason Code 2308 v7.23 3802 $49.99 2308 v7.23 3802 $29.99 D 2308 v7.23 3809 $33.00 2816 v7.23 3809 $96.00 2917 v8.07 5039 $57.35 D 2917 v8.07 5039 $93.18 2917 v8.07 5039 $63.07 D |
#13
|
|||
|
|||
If And Help?
Hmm. I see your view on the unique row being the lowest value, and
that makes sense However, what it is we are looking at is basically overpayments. We have it listed as paid. If a claim number, rate code, and R. Code is the same, we are flagging the lower of the amounts as d for us to investigate the overpayment, somehow, some way, a bill was submitted twice and they paid a part of it twice. A claim number, rate code, and R. Code should only have one paid amount. So, uniques are fine. The 2nd part, when I said row 5, I was counting the header row. If we don't count the header row, then yes, row 5 should have been flagged d which it was. Any ideas why the last one wasn't flagged? It flagged the correct two for the claims 2308. Is there anyway to have it leave the unique rows as blank, no code? 2917 v8.07 5039 $57.35 D 2917 v8.07 5039 $93.18 2917 v8.07 5039 $63.07 |
#14
|
|||
|
|||
If And Help?
I'll work on the unique problem in a minute; but, based on your other
comments, I may not be fully understanding your business model. When you say the "correct two for the claims 2308" were flagged, that is because there was a difference in the R.Code (the first two entries had identical Claims, Codes and R.Codes... the third one (as far as I am concerned) was unique (because its R.Code differed). Your comments make me think you see a linkage here that I am missing (which would affect how I constructed my formulas). Is there some over-riding control (such as the Claim number) which binds the groupings? Also, when you ask why the "last one wasn't flagged", that make me now think you want all but the highest price flagged... is that correct? I mean, the last three have identical Claims, Codes and R.Codes, so there are three amounts to look at... are you saying you want the lowest *two* prices flagged? And if there were four matching records, then you would want the lowest *three* prices flagged? If so, I missed that completely from your initial posting. Rick wrote in message ... Hmm. I see your view on the unique row being the lowest value, and that makes sense However, what it is we are looking at is basically overpayments. We have it listed as paid. If a claim number, rate code, and R. Code is the same, we are flagging the lower of the amounts as d for us to investigate the overpayment, somehow, some way, a bill was submitted twice and they paid a part of it twice. A claim number, rate code, and R. Code should only have one paid amount. So, uniques are fine. The 2nd part, when I said row 5, I was counting the header row. If we don't count the header row, then yes, row 5 should have been flagged d which it was. Any ideas why the last one wasn't flagged? It flagged the correct two for the claims 2308. Is there anyway to have it leave the unique rows as blank, no code? 2917 v8.07 5039 $57.35 D 2917 v8.07 5039 $93.18 2917 v8.07 5039 $63.07 |
#16
|
|||
|
|||
If And Help?
First, I would like to say I really appreciate everything.
Yes, I want all but the highest price one flagged. If the claim numbers, code, and r.code match, I want all but the highest priced one flagged. I want unique records unflagged. I messed up in my response previously, so sorry for the confusion. I messed up when I said the 2308's were flagged correctly. the third row of data (2308 v7.23 3809 $33.00) should not have been flagged since it is unique. I guess to summarize it, unique records should not be flagged. The way we link the data and review it is as follows: If the claim number, code, r.code match (or are duplicated, such as multiple rows with the same 3 fields) then we want all the lower paid values marked as D for reason code. 2308 v7.23 3802 $49.99 2308 v7.23 3802 $29.99 D 2308 v7.23 3802 $19.99 D 2307 v7.23 3802 $27.99 *** would not be marked since claim number is different *** The bottom two would be flagged since the claim, code, and r.cod are the same, so we want to mark all but the line with the highest value. If another has the same code, and r.code, but a different claim line, then that would be unmarked. I guess the easiest way for me to say it, and maybe I should have from the start, treat claim, code, and r.code as what must be locked in. For comparison purpose (not wanting it to look like this): This is just showing that ultimately the data is these three columns must match (claim, code, r.code), and is what is looked at in determining which is lower. 2308v7.233802 $49.99 2308v7.233802 $29.99 D 2308v7.233802 $19.99 D 2307v7.233802 $27.99 ***would not be marked since it doesn't fit the criteria, 2307v7.233802 is not equal to the rest in the list*** So for the below, we look at: 2917 v.807 5039's. We would mark the non highest values, so in below, we would mark the $57.35, and $63.07 values. What binds them together is, Claim number, code, and r.code must be the same. If claim number is different, but code and r.code match others, then it is not the same. All 3 fields must be the same and then we look at the 4th field (paid) and mark all but the highest value one. G L M S U Claim Code R. Code Paid Reason Code 2308 v7.23 3802 $49.99 2308 v7.23 3802 $29.99 D 2308 v7.23 3809 $33.00 D 2816 v7.23 3809 $96.00 D 2917 v8.07 5039 $57.35 D 2917 v8.07 5039 $93.18 2917 v8.07 5039 $63.07 |
#17
|
|||
|
|||
If And Help?
Works like a charm, thank you Lars!
Rick, Lars, I REALLLY appreciate you guys taking the time to help me. I am just learning arrays and am self teaching myself alot based off researching/reading other posts that people have helped others through. I really do appreciate both you taking the time and effort to help me. Thank you SOOO much! |
#18
|
|||
|
|||
If And Help?
I am pretty sure that this slight shorter (less calculations, one less
function call) array-entered** formula also does what you want... =IF(MAX(IF((G$2"")*(G$2:G$10000=G2)*(L$2:L$10000 =L2)*(M$2:M$10000=M2)=1,S$2:S$10000,""))S2,"D","" ) ** commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick wrote in message ... Works like a charm, thank you Lars! Rick, Lars, I REALLLY appreciate you guys taking the time to help me. I am just learning arrays and am self teaching myself alot based off researching/reading other posts that people have helped others through. I really do appreciate both you taking the time and effort to help me. Thank you SOOO much! |
#19
|
|||
|
|||
If And Help?
Even shorter if you remove the
(G$2"")* in the beginning of the IF formula. Lars-Åke On Mon, 11 Aug 2008 15:07:01 -0400, "Rick Rothstein \(MVP - VB\)" wrote: I am pretty sure that this slight shorter (less calculations, one less function call) array-entered** formula also does what you want... =IF(MAX(IF((G$2"")*(G$2:G$10000=G2)*(L$2:L$1000 0=L2)*(M$2:M$10000=M2)=1,S$2:S$10000,""))S2,"D"," ") ** commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick wrote in message ... Works like a charm, thank you Lars! Rick, Lars, I REALLLY appreciate you guys taking the time to help me. I am just learning arrays and am self teaching myself alot based off researching/reading other posts that people have helped others through. I really do appreciate both you taking the time and effort to help me. Thank you SOOO much! |
#20
|
|||
|
|||
If And Help?
Yes, you are correct! In a much earlier version of the formula, that term
was needed to prevent a value printing out if you copied the formula down past the end of the data. Somewhere along the line in my development, that test became unnecessary and I simply never looked back at it to question whether it was needed anymore or not. Thanks for spotting that. For the archives, the final array-entered** formula I am proposing is this... =IF(MAX(IF((G$2:G$10000=G2)*(L$2:L$10000=L2)*(M$2: M$10000=M2)=1,S$2:S$10000,""))S2,"D","") Rick "Lars-Åke Aspelin" wrote in message ... Even shorter if you remove the (G$2"")* in the beginning of the IF formula. Lars-Åke On Mon, 11 Aug 2008 15:07:01 -0400, "Rick Rothstein \(MVP - VB\)" wrote: I am pretty sure that this slight shorter (less calculations, one less function call) array-entered** formula also does what you want... =IF(MAX(IF((G$2"")*(G$2:G$10000=G2)*(L$2:L$100 00=L2)*(M$2:M$10000=M2)=1,S$2:S$10000,""))S2,"D", "") ** commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick wrote in message ... Works like a charm, thank you Lars! Rick, Lars, I REALLLY appreciate you guys taking the time to help me. I am just learning arrays and am self teaching myself alot based off researching/reading other posts that people have helped others through. I really do appreciate both you taking the time and effort to help me. Thank you SOOO much! |
Thread Tools | |
Display Modes | |
|
|