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