L
Lars-Åke Aspelin
It is also possible, but maybe not considered good style, to remove
,""
in the very end of the inner IF statement.
Lars-Åke
,""
in the very end of the inner IF statement.
Lars-Åke
V
vidguru
You both rock! Thank you VERY much!
V
vidguru
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!
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 wayto 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 filledit 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!
V
vidguru
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
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 amjust twiddling my thumbs and not trying to figure this out
L
Lars-Åke Aspelin
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$10=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
=IF(OR(MAX(IF((G$2:G$10=G2)*(L$2:L$10=L2)*(M$2:M$10=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
V
vidguru
THANKS SOOO MUCH! I can't thank you enough!
R
Rick Rothstein \(MVP - VB\)
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$10=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
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$10=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
R
Rick Rothstein \(MVP - VB\)
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
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
V
vidguru
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
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
V
vidguru
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.
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.
V
vidguru
The last post was to summarize what I said above. I think it is
clearer and less confusing
clearer and less confusing