How to extract data from a column-complex formula perhaps?

Excel@excel 45 Reputation points
2026-09-16T14:00:06.71+00:00

Rule: A member can be paid a maximum of 4 units per date of service, regardless of the combination of codes listed in Column E(code). If more than 4 units are paid, the excess units are not payable and must be identified for retraction.

Based on this rule, I generated a report containing thousands of claim lines that exceed the 4-unit limit. Column G ("total Units Paid") shows the total units paid for each unique claim ID and date of service that exceeded the 4-unit maximum. The report has this total on each claim line for that date and claim id.

 Example 1

Claim ID 2343 (Member: Rose)

  • Date of Service: 9/23/2024
  • 5 claim lines were paid
  • Total units paid = 5

Since only 4 units are payable, I need to identify and extract/highlight the line representing the additional 1 unit that should be retracted. Any line can be extracted, as long as 4 units maximum are paid.

 

Example 2

Claim ID 2670 (Member: Adam)

  • Date of Service: 7/13/2026
  • 2 claim lines were paid
  • Total units paid = 7

In this case, 3 units must be retracted. I'm unsure how to determine which line(s) should be identified.

  • Highlight both claim lines because the claim exceeds the limit by 3 units, or
  • Highlight only the line containing 5 units and retract 3 units from that line?

 I need help with any formulas and how to achieve this result. Please be detailed in your explanation since I am not an expert in Excel.

 I hope I'm explaining this clearly. Any guidance on the best approach would be greatly appreciated.

Thank you!

User's image

Microsoft 365 and Office | Excel | For business | Windows
Answer accepted by question author
Heimerdinger 630 Reputation points Independent Advisor
2026-09-16T16:01:37.1766667+00:00

Hi @Excel@excel, 

You can solve this by adding a helper column that calculates the exact number of units to retract from each claim line. For the result you described, I recommend allocating the excess units to the first available claim line until the excess is fully accounted for, rather than highlighting every line in the group. 

Based on the rows shown:

  • Claim 2343 has 5 total units, so retract 1 unit from one line only. 
  • Claim 2670 has 7 total units, so retract 3 units from the 5-unit line and keep the 2-unit line unchanged. 
  • Claim 9000 has 5 total units, so retract 1 unit from one line only. 
  • Claim 7200 has 5 total units, so retract 1 unit from one line only. 
  • Claim 3100 has 5 total units, so retract 1 unit from one line only. 

This approach keeps the total payable units at exactly 4 for each unique combination of Claim ID + Date of Service. 

Step 1: Add an “Units to Retract” column 

Your current columns appear to be: 

User's image

In H1, enter: "Units to Retract" User's image

In H2, enter this formula: 

=MAX(0,MIN(MAX(0,$G2-4),SUMIFS($F$2:F2,$A$2:A2,$A2,$D$2:D2,$D2))-MIN(MAX(0,$G2-4),SUMIFS($F$2:F2,$A$2:A2,$A2,$D$2:D2,$D2)-$F2))The formula allocates the excess units to the first line or lines in the group until the complete excess has been assigned. 

For example Claim 2670: 

User's image

Step 2: Add a readable action column 

In I1, enter: "Action"

User's image

In I2, enter:  

=IF(H2>0,"Retract "&H2&IF(H2=1," unit"," units"),"Keep") 

Step 3: Highlight the lines requiring retraction 

To highlight the entire claim line: 

  1. Select the report data, for example A2:I10000. 
  2. On the Home tab, select Conditional Formatting. 
  3. Select New Rule. 
  4. Select Use a formula to determine which cells to format. 
  5. Enter:  =INDEX($H:$H,ROW())>0  User's image
  6. Select Format and choose a light red or orange fill. User's image User's image
  7. Select OK. 

Only rows with units assigned for retraction will be highlighted. Conditional formatting applies a format based on a rule or cell value. 

User's image

You may refer to the AI-generated answer provided in the thread, or you may try the steps I shared above, which I have tested on my side using the sample data provided.

If I have misunderstood any part of your situation or requirements, please feel free to let me know so I can provide more relevant guidance.

Thank you for your time and understanding.

Was this answer helpful?

1 person found this answer helpful.
Answer accepted by question author

AI answer

2026-09-16T14:00:37.9966667+00:00

A practical Excel approach is to calculate the running units within each Claim ID + Date of Service group, then mark only the units above 4 as retractable.

If the goal is “pay up to 4 units total, retract the excess units”, then the clean rule is:

  • keep lines until cumulative paid units reaches 4
  • any units after that are excess
  • if a line crosses the limit, retract only the portion above 4 from that line

For the Adam example:

  • first line = 5 units
  • second line = 2 units
  • total = 7
  • only 4 units are allowed
  • excess = 3 units

So the best result is:

  • retract 1 unit from the first 5-unit line if that line is processed first and only 4 can remain there, or
  • if using row order as the decision rule, calculate excess by cumulative order and retract from the line where the total goes above 4

A consistent Excel method is to use the row order already in the report.

Assume these columns:

  • A = Claim ID
  • B = Claim Line
  • C = Member Name
  • D = Date
  • E = Code
  • F = Billed Units
  • G = Total Units Paid

Add these helper columns:

  • H = Running Units
  • I = Retract Units
  • J = Flag

Use these formulas starting in row 2.

1. Running Units in H2

=SUMIFS($F$2:F2,$A$2:A2,A2,$D$2:D2,D2)

This adds billed units from the top of the sheet down to the current row, but only for the same:

  • Claim ID
  • Date

So for Claim 2343 on 9/23/2024, the running units would be:

  • row 1 = 1
  • row 2 = 2
  • row 3 = 3
  • row 4 = 4
  • row 5 = 5

2. Retract Units in I2

=MAX(0,H2-4)-MAX(0,H2-F2-4)

This formula returns only the number of units on the current line that exceed the 4-unit limit.

How it works:

  • H2 = cumulative units including this row
  • H2-F2 = cumulative units before this row
  • it calculates how much excess exists after this row, then subtracts any excess that already existed before this row
  • result = excess attributable to this line only

3. Flag in J2

=IF(I2>0,"Retract","")

Copy all formulas down.

What the result looks like

For Claim 2343 with five 1-unit lines:

  • running units = 1, 2, 3, 4, 5
  • retract units = 0, 0, 0, 0, 1
  • only the 5th line is flagged

For Claim 2670 with 5 units on one line and 2 on another:

If the 5-unit line appears first:

  • row 1 running units = 5 → retract units = 1
  • row 2 running units = 7 → retract units = 2

So:

  • first line: retract 1
  • second line: retract 2
  • total retraction = 3

This is usually the most defensible spreadsheet method because it applies the rule consistently by line order.

If instead the business rule is to retract from the largest-unit line first, that would require a different setup, because Excel would need to rank lines within each Claim ID + Date group before assigning the excess.

If only rows with total over 4 should be processed

Since column G already contains the total units paid for the claim/date group, the flag can be limited to those groups:

=IF(G2>4,MAX(0,H2-4)-MAX(0,H2-F2-4),0)

Then the text flag becomes:

=IF(I2>0,"Retract","")

To highlight the rows automatically

Use Conditional Formatting on the data rows.

  1. Select the rows to highlight.
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. Enter:
=$I2>0
  1. Choose a fill color.
  2. Select OK.

That will highlight only lines with retractable units.

To extract only the lines that need retraction

If Excel supports FILTER, use a formula like this in another area:

=FILTER(A2:J1000,I2:I1000>0)

That returns only the rows where retract units are greater than 0.

Summary

Use this logic:

  • calculate cumulative units by Claim ID + Date
  • assign excess only when cumulative units go above 4
  • retract only the portion of each line that exceeds 4

Core formulas:

Running Units

=SUMIFS($F$2:F2,$A$2:A2,A2,$D$2:D2,D2)

Retract Units

=IF(G2>4,MAX(0,H2-4)-MAX(0,H2-F2-4),0)

Flag

=IF(I2>0,"Retract","")

If needed, the same method can be adapted to return the result in a single formula column without helper columns, but helper columns are much easier to audit on a large claims report.

References:

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

1 additional answer

Sort by: Newest
Most helpful Newest Oldest
  1. Marcin Policht 109.5K Reputation points MVP Volunteer Moderator
    2026-09-16T16:04:23.01+00:00

    Likely the cleanest approach would be to calculate the number of units that must be retracted for each Claim ID + Date of Service, and then assign those retractions to specific claim lines. Since your rule says that any combination of codes is allowed as long as no more than 4 total units remain payable, you do not need to retract entire claim lines. You can retract a specific number of units from one line when that line has enough units to absorb the entire excess.

    For your examples, Claim 2343 has 5 total units, so 1 unit must be retracted. Claim 2670 has 7 total units, so 3 units must be retracted. For Adam, if the two lines contain 5 units and 2 units, you can retract all 3 excess units from the 5-unit line. That leaves 2 units on that line plus 2 units on the other line, for a total of 4 payable units. You do not need to highlight both lines.

    Assuming your columns are exactly as shown:

    A = Claim ID B = Claim line C = Member Name D = Date E = Code F = Billed Units G = Total Units Paid

    I would add two new columns:

    H = Units to Retract I = Units Payable

    The first step is to calculate the units to retract for the entire Claim ID + Date combination. You already have that information in column G, so H can start with a very simple formula:

    =MAX(0,G2-4)

    For Claim 2343, G is 5, so H returns 1. For Claim 2670, G is 7, so H returns 3.

    However, that formula puts the same 1 or 3 on every line belonging to the claim, which is not what you want. We need to determine which specific line should carry the retraction.

    If you want to use your Adam example exactly as described, where the 5-unit line absorbs all 3 excess units, my suggestion would be to use this approach. First, identify the excess:

    =MAX(0,G2-4)

    Then identify whether the current line has enough units to absorb that entire excess. For example, in H2:

    =IF(AND(G2>4,F2>=G2-4),G2-4,0)

    For Adam, the 5-unit line would receive a retraction of 3, because it has 5 units and the claim has 3 excess units. The 2-unit line would receive 0. The resulting payable units would be 2 + 2 = 4.

    However, that formula has a limitation - it works when at least one individual line contains enough units to absorb the entire excess. You have thousands of claim lines, so you should account for cases where the excess cannot be taken from one line.

    For example:

    Claim total = 7 Line 1 = 2 units Line 2 = 2 units Line 3 = 3 units

    The excess is 3, so you could retract all 3 from Line 3.

    But:

    Claim total = 7 Line 1 = 2 Line 2 = 2 Line 3 = 2 Line 4 = 1

    The excess is still 3, but no single line contains 3 units. Therefore, the formula must retract units from multiple lines.

    A more robust method is to distribute the retraction automatically based on the order of the claim lines. This should guarantee that exactly 4 units remain payable for every Claim ID + Date.

    Add column H called Cumulative Units. In H2 enter:

    =SUMIFS($F$2:F2,$A$2:A2,A2,$D$2:D2,D2)

    Copy this formula down. This calculates the cumulative billed units for the current Claim ID and Date.

    Then add column I called Units to Retract. In I2 enter:

    =MIN(F2,MAX(0,H2-4))

    Copy it down.

    Then add column J called Units Payable. In J2 enter:

    =F2-I2

    Copy it down.

    Using your data, the result would look conceptually like this:

    Claim Line Billed Total Paid Cumulative Retract Payable
    2343 1 1 5 1 0 1
    -------- -------- -------- -------- -------- -------- --------
    2343 1 1 5 1 0 1
    2343 2 1 5 2 0 1
    2343 3 1 5 3 0 1
    2343 4 1 5 4 0 1
    2343 5 1 5 5 1 0
    2670 2 5 7 5 1 4
    2670 4 2 7 7 2 0

    Notice that this produces a slightly different result from your proposed Adam example. The formula retracts 1 from the 5-unit line and 2 from the 2-unit line. The result is still exactly 4 payable units.

    If you specifically want Excel to take all 3 excess units from Adam's 5-unit line, rather than splitting the retraction, that is also possible. In fact, because you stated that any line can be selected, I would use a "single-line if possible, otherwise distribute" approach. That would identify a line whose billed units are at least equal to the excess and put the entire retraction there. If no individual line can absorb the excess, Excel would distribute the retraction across multiple lines.

    I would not highlight every line where Total Units Paid > 4. Column G is a claim/date-level total, so every line for Adam correctly shows 7, but that does not mean every line needs to be retracted. The retraction should be attached to specific units on specific lines.

    If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

    hth

    Marcin

    Was this answer helpful?

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.