Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
Here is what I need to do:
Column G contains a claim number. L contains a combination of letters and numbers, its a code, like v3.7 etc. Column M contains a code, a 4 digit number. Column S is paid amount. T is Overpayment. Column U is reason code. I am looking for a macro, or even maybe an if and statement with an offset that can do the below: G L M S T U Claim Code R. Code Paid Overpayment 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 etc.. The data is sorted, by multiple fields. Claim number, code, r. code. I want a macro, or possibly and if and then statement that will look at for: If G2 = G3 and L2 =L3 and M2=M3 then I want it to put a D offset into Column U for whichever is the smaller of the amounts in the overpaid column.. So, in this example, the D would go into Column U3. If they don't match, such as line 2308 and 2816 I want it to be blank. There may be times that there may be multiple one with the claim number, code, and r code being the same if possible. Documents are several thousand lines long, so you can see, going through manually comparing these take a long time. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
On Sat, 09 Aug 2008 14:18:44 GMT, Lars-Åke Aspelin
wrote: On Sat, 9 Aug 2008 07:10:02 -0700 (PDT), wrote: 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 Why is there a D in both the bottom line and in the third line from the bottom in this example. You wrote that you wanted a D "for whichever is the smaller". Do you mean that you want a D "for all but the largest"? Please explain. Lars-Åke Assuming that the last D in the example is a typo and that the data in columns (Paid) are numbers formatted as Currency ($) rather than text, you may try the following formula in cell U2 =IF(AND(SUMPRODUCT((G$2:G$100=G2)*(L$2:L$100=L2)) 1,S2=SMALL(S$2:S$100*(G$2:G$100=G2)*(L$2:L$100=L2) ,ROWS(S$2:S$100)+1-SUMPRODUCT((G$2:G$100=G2)*(L$2:L$100=L2)))),"D","" ) Note: This is an array formula that has to be entered with CTRL+SHIFT+ENTER rather than just ENTER. Copy the cell down column U as far as needed. Change all 100 to something bigger to cover all your data rows Hope this helps / Lars-Åke |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
See response in public.excel.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
Does this do what you want?
=IF(OR(AND(G3=G4,L3=L4,M3=M4,S4<S3),AND(G4=G5,L4=L 5,M4=M5,S4<S5)),"D","") Please don't multipost to different groups, it just fragments your replies. If you want to post to more than one group then crosspost by putting all the groups in the Newsgroup window. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
This array-entered** formula should do will do what you want (and it will
work correctly even if the 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, but if you 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
You have multiple answers to your question in more than one newsgroup.
Please consider the following for any future questions you may ask... From a post by Jeff Johnson: "You have posted this question individually to multiple groups. This is called Multiposting and it's BAD. Replies made in one group will not be visible in the other groups, which may cause multiple people to respond to your question with the same answer because they didn't know someone else had already done it. This is a waste of time. If you MUST post your message to multiple groups, post a single message and select all the groups (or type their names manually in the Newsgroups field, separated by commas) in which you want it to be seen. This is called Crossposting and when used properly it is GOOD." Some additional comment previously posted by me: "You may not see this as a problem, but those of us who volunteer answering questions on newsgroups do see it as a problem. You can't imagine how annoying it is for a volunteer to read a question, research background material, test sample code and then formulate and post an answer to the original question only to go to another newsgroup and find the question posted and ALREADY answered over there. On top of that, if you cross-post your question, all of the readers in all the newsgroups it is cross-posted to see both the original question and all of the answers given to it. This is beneficial to you because then we can add additional material to, add clarification to, as well as add additional examples to an answer you have received previously... that means you end up with a more complete solution to your problem. This is a win-win situation for all of us." Rick |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
Thanks. The last D was not a typo. Sometimes we might have the same
code and rate code listed more than twice, maybe 3, 4, 5 times. We need it to mark all the ones that are smaller than the highest value with a D. On Aug 9, 9:56*am, Lars-Åke Aspelin wrote: On Sat, 09 Aug 2008 14:18:44 GMT, Lars-Åke Aspelin wrote: On Sat, 9 Aug 2008 07:10:02 -0700 (PDT), wrote: 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 Why is there a D in both the bottom line and in the third line from the bottom in this example. You wrote that you wanted a D "for whichever is the smaller". Do you mean that you want a D "for all but the largest"? Please explain. Lars-Åke Assuming that the last D in the example is a typo and that the data in columns (Paid) are numbers formatted as Currency ($) rather than text, you may try the following formula in cell U2 =IF(AND(SUMPRODUCT((G$2:G$100=G2)*(L$2:L$100=L2)) 1,S2=SMALL(S$2:S$100*(G$2:G$100=G2)*(L$2:L$100=L2) ,ROWS(S$2:S$100)+1-SUMPRODUCT((G$2:G$100=G2)*(L$2:L$100=L2)))),"D","" ) Note: This is an array formula that has to be entered with CTRL+SHIFT+ENTER rather than just ENTER. Copy the cell down column U as far as needed. Change all 100 to something bigger to cover all your data rows Hope this helps / Lars-Åke |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
It is also possible, but maybe not considered good style, to remove
,"" in the very end of the inner IF statement. Lars-Åke On Mon, 11 Aug 2008 16:09:22 -0400, "Rick Rothstein \(MVP - VB\)" wrote: 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$10 000=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! |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
You both rock! Thank you VERY much!
|
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
Gentlemen, 1 last question. The formula works GREAT! I can't stress
that enough. It didn't dawn on me until last night: Sometimes we have instances that there are duplicates (multiple rows) with the same claim, code, r.code, and paid amount. Right now the formula is marking all values that meet the criteria (same claim, code, r.code) that is less than the max amount. That is great :) Is there a way to also have it mark any others that meet the criteria (same claim, code, r.cod) that have the same paid amount? So, for instance, below, I added the last 2 lines just now. I still want it to do what it did before, but also mark all but one of the max amounts... If not clear, I have my work around below this. I'm sure I can save some computing power and time if it can be added into the array. If not, no biggie :) I appreciate everything you guys have done :) 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 3802 $33.00 D 2308 v7.23 3802 $49.99 D 2308 v7.23 3802 $49.99 D My Work Around: I created 2 more columns, V&W I entered in the formula you guys created :) as an array, and filled it all the way down in column V In column W I did this formula: =IF(AND(G2=G3,L2=L3,M2=M3,S2=S3),"D","") and filled it all the way down. I copy and paste special: values for both column V&W. I autofilter both individually for blanks, selected visible, deleted blanks. In column U: =IF(V2&W2="", "", "D") autofilled it down so I know if a d has been placed in either V or W. My work around would work, but it is flawed since we have excel 03 vs the new one, we can only sort by 3 fields and the duplicate (equal) charge amounts might not always be together as in the example. Yay! |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
The only other thing I can think about doing is creating a column and
do =G2&" "&L2&" "&M2 and fill it down. Sort by this new column, then by paid amount and then do my =IF(AND(G2=G3,S2=S3),"D","") and filled it all the way down. Either way, I think mine is just adding a bunch of extra steps and wasting computing power when I am sure there is a better way :) I keep adding my thoughts so you guys don't think I am just twiddling my thumbs and not trying to figure this out :) |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
Try this formula in cell U2 and copy down.
=IF(OR(MAX(IF((G$2:G$10=G2)*(L$2:L$10=L2)*(M$2:M$1 0=M2)=1,S$2:S$10))S2,SUM((G$2:G2=G2)*(L$2:L2=L2)* (M$2:M2=M2)*(S$2:S2=S2))1),"D","") This will "D-mark" all but the first of the rows that are identical in all four columns (G, L, M and S) in addition to the previous criteria. Hope this helps / Lars-Åke On Tue, 12 Aug 2008 10:06:54 -0700 (PDT), wrote: Gentlemen, 1 last question. The formula works GREAT! I can't stress that enough. It didn't dawn on me until last night: Sometimes we have instances that there are duplicates (multiple rows) with the same claim, code, r.code, and paid amount. Right now the formula is marking all values that meet the criteria (same claim, code, r.code) that is less than the max amount. That is great :) Is there a way to also have it mark any others that meet the criteria (same claim, code, r.cod) that have the same paid amount? So, for instance, below, I added the last 2 lines just now. I still want it to do what it did before, but also mark all but one of the max amounts... If not clear, I have my work around below this. I'm sure I can save some computing power and time if it can be added into the array. If not, no biggie :) I appreciate everything you guys have done :) 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 3802 $33.00 D 2308 v7.23 3802 $49.99 D 2308 v7.23 3802 $49.99 D My Work Around: I created 2 more columns, V&W I entered in the formula you guys created :) as an array, and filled it all the way down in column V In column W I did this formula: =IF(AND(G2=G3,L2=L3,M2=M3,S2=S3),"D","") and filled it all the way down. I copy and paste special: values for both column V&W. I autofilter both individually for blanks, selected visible, deleted blanks. In column U: =IF(V2&W2="", "", "D") autofilled it down so I know if a d has been placed in either V or W. My work around would work, but it is flawed since we have excel 03 vs the new one, we can only sort by 3 fields and the duplicate (equal) charge amounts might not always be together as in the example. Yay! |
#26
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
THANKS SOOO MUCH! I can't thank you enough!
|
#27
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
Time to add the (G$2:G$24<"") term back in; otherwise the second and
subsequent blank rows get tagged with a "D". The final array-entered** formula is... =IF(OR(MAX(IF((G$2:G$10=G2)*(L$2:L$10=L2)*(M$2:M$1 0=M2)=1,S$2:S$10))S2,SUM((G$2:G2<"")*(G$2:G2=G2) *(L$2:L2=L2)*(M$2:M2=M2)*(S$2:S2=S2))1),"D","") ** commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick "Lars-Åke Aspelin" wrote in message ... Try this formula in cell U2 and copy down. =IF(OR(MAX(IF((G$2:G$10=G2)*(L$2:L$10=L2)*(M$2:M$1 0=M2)=1,S$2:S$10))S2,SUM((G$2:G2=G2)*(L$2:L2=L2)* (M$2:M2=M2)*(S$2:S2=S2))1),"D","") This will "D-mark" all but the first of the rows that are identical in all four columns (G, L, M and S) in addition to the previous criteria. Hope this helps / Lars-Åke On Tue, 12 Aug 2008 10:06:54 -0700 (PDT), wrote: Gentlemen, 1 last question. The formula works GREAT! I can't stress that enough. It didn't dawn on me until last night: Sometimes we have instances that there are duplicates (multiple rows) with the same claim, code, r.code, and paid amount. Right now the formula is marking all values that meet the criteria (same claim, code, r.code) that is less than the max amount. That is great :) Is there a way to also have it mark any others that meet the criteria (same claim, code, r.cod) that have the same paid amount? So, for instance, below, I added the last 2 lines just now. I still want it to do what it did before, but also mark all but one of the max amounts... If not clear, I have my work around below this. I'm sure I can save some computing power and time if it can be added into the array. If not, no biggie :) I appreciate everything you guys have done :) 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 3802 $33.00 D 2308 v7.23 3802 $49.99 D 2308 v7.23 3802 $49.99 D My Work Around: I created 2 more columns, V&W I entered in the formula you guys created :) as an array, and filled it all the way down in column V In column W I did this formula: =IF(AND(G2=G3,L2=L3,M2=M3,S2=S3),"D","") and filled it all the way down. I copy and paste special: values for both column V&W. I autofilter both individually for blanks, selected visible, deleted blanks. In column U: =IF(V2&W2="", "", "D") autofilled it down so I know if a d has been placed in either V or W. My work around would work, but it is flawed since we have excel 03 vs the new one, we can only sort by 3 fields and the duplicate (equal) charge amounts might not always be together as in the example. Yay! |
#28
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
Follow up note.... I modified the formula that Lars-Åke posted and it was
set to the exact size of the data, but this is not necessary. However, if you do extend the coverage of rows to handle future data, then the G$2:G$2<"" term (note I mistaken used G$24 for the end range in my previous write up, but used it correctly in the formula) must be added to protect the blank rows the formula is placed in. So, if you think you will have up to 5000 rows of data eventually, you can put this array-entered** formula in U2 (the original column the OP said he wanted the "D" in)... =IF(OR(MAX(IF((G$2:G$5000=G2)*(L$2:L$5000=L2)*(M$2 :M$5000=M2)=1,S$2:S$5000))S2,SUM((G$2:G2<"")*(G$ 2:G2=G2)*(L$2:L2=L2)*(M$2:M2=M2)*(S$2:S2=S2))1)," D","") and copy it down for 5000 rows and the display will be correct for both empty rows and rows with data in them. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Time to add the (G$2:G$24<"") term back in; otherwise the second and subsequent blank rows get tagged with a "D". The final array-entered** formula is... =IF(OR(MAX(IF((G$2:G$10=G2)*(L$2:L$10=L2)*(M$2:M$1 0=M2)=1,S$2:S$10))S2,SUM((G$2:G2<"")*(G$2:G2=G2) *(L$2:L2=L2)*(M$2:M2=M2)*(S$2:S2=S2))1),"D","") ** commit the formula using Ctrl+Shift+Enter, not just Enter by itself Rick "Lars-Åke Aspelin" wrote in message ... Try this formula in cell U2 and copy down. =IF(OR(MAX(IF((G$2:G$10=G2)*(L$2:L$10=L2)*(M$2:M$1 0=M2)=1,S$2:S$10))S2,SUM((G$2:G2=G2)*(L$2:L2=L2)* (M$2:M2=M2)*(S$2:S2=S2))1),"D","") This will "D-mark" all but the first of the rows that are identical in all four columns (G, L, M and S) in addition to the previous criteria. Hope this helps / Lars-Åke On Tue, 12 Aug 2008 10:06:54 -0700 (PDT), wrote: Gentlemen, 1 last question. The formula works GREAT! I can't stress that enough. It didn't dawn on me until last night: Sometimes we have instances that there are duplicates (multiple rows) with the same claim, code, r.code, and paid amount. Right now the formula is marking all values that meet the criteria (same claim, code, r.code) that is less than the max amount. That is great :) Is there a way to also have it mark any others that meet the criteria (same claim, code, r.cod) that have the same paid amount? So, for instance, below, I added the last 2 lines just now. I still want it to do what it did before, but also mark all but one of the max amounts... If not clear, I have my work around below this. I'm sure I can save some computing power and time if it can be added into the array. If not, no biggie :) I appreciate everything you guys have done :) 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 3802 $33.00 D 2308 v7.23 3802 $49.99 D 2308 v7.23 3802 $49.99 D My Work Around: I created 2 more columns, V&W I entered in the formula you guys created :) as an array, and filled it all the way down in column V In column W I did this formula: =IF(AND(G2=G3,L2=L3,M2=M3,S2=S3),"D","") and filled it all the way down. I copy and paste special: values for both column V&W. I autofilter both individually for blanks, selected visible, deleted blanks. In column U: =IF(V2&W2="", "", "D") autofilled it down so I know if a d has been placed in either V or W. My work around would work, but it is flawed since we have excel 03 vs the new one, we can only sort by 3 fields and the duplicate (equal) charge amounts might not always be together as in the example. Yay! |
#29
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
I have one more question. I'm not sure if this can be written into
the array, or if I would maybe have to do a second column and do some comparison. The final array worked PERFECT! In the example, I changed column M to DOS. In my project, I added *(info) to it to add it as a criteria in the formula, won't do it here since there is limited room for the table to display properly. There is one more stipulation/rule that I found out. Column U is showing the old formulas results. I have added 2column V&W V show what the results should be. Column W basically pulls what another columns code means, the name. There might be the multiple codes with the same name. (In my project, I have added that code column into the array forumla as the *(info) to have it lock down to the row.Basically there is one master rule, clinic trumps all. It's hard to explain, so I will explain off the below example. Before we had the formula marking such as below. The below (for column U) is looking at claim, code, dos, paid and marking all d if they are NOT the max amound, (marking all other equal to the max amount except 1), and not marking the uniques. Well, column V is looking at the same info, EXCEPT... if claim, dos, and (column W), has clinic (note: column L doesn't matter. Normally we look at column L as an identifier, but IF claim and DOS match and any of the records contain Clinic in W that over rides everything), then mark everything with a D except the highest clinic amount. So, in the below example, Column V is my wanted results. Since clinic is present for that claim, on that dos, all others but 1 of the highest (the highest) are marked with D. Look at the line with claim 2308, code v7.53 and v7.73 and dos 7/31/06. With the above formula we wouldn't have these marked, since they ARE not "dups" techinically. However, if column W contains clinic at all for that claim, dos it needs to be marked since this field THEN will not matter. Again, Clinic in a claim/dos trumps all. If Clinic is NOT present for that claim, that date, then proceed with the previous array/formlua. The only value in W that is of concern is the word clinic, any other value means nothing to us. This is probably confusing as all hell, so hopefully the below example will clarify. G L M S U V W Claim Code DOS Paid Code want 2308 v7.23 7/31/06 $49.99 D Res 2308 v7.23 7/31/06 $29.99 D D meds 2308 v7.23 7/31/06 $33.00 D D Clinic 2308 v7.23 7/31/06 $42.00 D Clinic 2308 v7.23 7/31/06 $49.99 D D shot 2308 v7.53 7/31/06 $49.99 D shot 2308 v7.73 7/31/06 $49.99 D shot 2308 v7.53 8/02/06 $49.99 Clinic 2308 v7.73 8/02/06 $79.99 D shot 2308 v7.73 8/05/06 $59.99 D Res 2308 v7.73 8/05/06 $79.99 D shot |
#30
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
There is another column that has been added that trumps everything.
We will call this column W. W contains the name look up of different codes. The above array worked AWESOME! Any way to add this criteria to it? In the above, we had it look at claim, code, r.code and had it mark all but the highest paid amount for that set, (it did not mark unique ones, and only left unmarked one of the highest). Well, I would like it to do the same thing, HOWEVER, maybe do this check first: If claim, code (locking claim and code together), if any of them list the word CLINIC in column W, then mark all but the one with the highest value (like above) with a d. If it does NOT contain CLINIC, then proceed with the above formula. |
#31
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If And Help?
The last post was to summarize what I said above. I think it is
clearer and less confusing :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|