How to retract lines based on lowest RVu for each code.

Excel@excel 45 Reputation points
2026-11-18T16:29:43.81+00:00

Rules and scenarios

  1. A member can be paid only a maximum of 4 units per date of service, regardless of the combination of codes listed in Column F. Each code is on different claim lines. If more than 4 units are paid per date of service per member, the excess units are not payable and must be identified for retraction.
  2.  Column H already calculates how many units total paid for a member per date of service. (Note: The same number of total units are displayed on each claim line for that date of service in column H.).But we must consider it only once. Example for Member Rose-A20, total 5 units paid for 9/23/2024.For Member John H100 6 units paid for 2/12/2026.
  3. A member can have multiple dates of service on same claim id example John-H100 OR Member can have multiple dates of service on different claim id , example Tom-G25. Regardless the 4 units rule applies to per member per date of service.
  4. Goal: Retract the unit where the code in Column F  has the lowest RVU in Column K
  5. If a line does not have enough units then we must go to the next least rvu value to retract the excess units beyond the 4 units.

 

The new example spreadsheet I copied has the helper columns H and I that was suggested in previpus psot. I added column K(RVU). The data for this is populated from a lookup formula which referencesExample-MicrosoftCommunity.png a lookup sheet

I also highlighted the rows that need to be retracted based on lowest RVU. This is the end result I am looking for.

For member Rose-A20 1 unit of 97032  should be retracted since 97032 has the lowest RVU at 0.25.

For member Adam-B20 3 units of 97761 should be retracted although it does not matter which line since both codes 97761 and 97760 have same rvu which is 0.50.

 Note: All the codes in Column F are valid codes. No need to exclude any codes.

Also this report has only claims that exceed 4 unit limit.

 

Microsoft 365 and Office | Excel | For business | Windows

2 answers

Sort by: Most helpful
  1. Kai-L 21,790 Reputation points Microsoft External Staff Moderator
    2026-11-18T17:08:18.4966667+00:00

    Hello Excel@excel,

    Thank you for the clear example and expected results. Based on your screenshot (Member ID in D, Date in E, billed units in G, total units paid in H and RVU in K), you can calculate the retraction without sorting or highlighting rows manually.

    In I2, enter this formula and copy it down:

    =MAX(0,MIN(G2,H2-4-SUMIFS($G$2:$G$1000,$D$2:$D$1000,D2,$E$2:$E$1000,E2,$K$2:$K$1000,"<"&K2)-SUMPRODUCT(($D$2:$D$1000=D2)($E$2:$E$1000=E2)($K$2:$K$1000=K2)(ROW($G$2:$G$1000)<ROW())$G$2:$G$1000)))

    Change 1000 to the last row of your report if needed.

    For each member and date of service, the formula works out the excess units (total paid minus 4). It then subtracts units already assigned to lines with a lower RVU, or to earlier lines with the same RVU. What's left is retracted from the current line, up to that line's billed units. If the lowest RVU line doesn't have enough units, the remaining units move to the next lowest RVU automatically. Claim ID isn't used, so the same claim with multiple dates (John) and different claims for the same member (Tom) are both handled correctly.

    In J2, enter this formula and copy it down:

    =IF(I2=0,"keep","Retract "&I2&" unit"&IF(I2=1,"","s"))

    When two lines have the same RVU, as in Adam's case, the formula retracts from the line that appears first in the sheet. Please make sure the dates, units and RVUs are stored as numbers, not text. Otherwise, the SUMIFS and comparisons won't match.

    I hope this helps. Looking forward to your reply.


    If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

    Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    Was this answer helpful?


  2. Excel@excel 45 Reputation points
    2026-11-18T16:36:25.05+00:00

    Example-MicrosoftCommunity.png

    This has the column names

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.