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.

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

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

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

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

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

OK, thanks for the clarification about "smaller".
I also noticed that I had forgotten to check the M column in my
previous formula.

Here is a modified formula that will put a D beside "all the ones that
are smaller than the highest value".

=IF(AND(SUMPRODUCT((G$2:G$100=G2)*(L$2:L$100=L2)*( M$2:M$100=M2))1,S2<LARGE(S$2:S$100*(G$2:G$100=G2) *(L$2:L$100=L2)*(M$2:M$100=M2),1)),"D","")

Hope this helps / Lars-Åke


On Mon, 11 Aug 2008 08:02:59 -0700 (PDT), wrote:

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

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

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

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

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

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

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

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