Calculating discount amount with respect to hierarchy of additional discounts

This request for help may end up requiring VBA, Access, or both, but I figured I would start in this forum and see what pops up.

I have a large (950k+ records) table of information that contains original amounts along with potential corresponding percentage and amount discounts. I'm trying to find a way to calculate each record's actual discount amount. The tricky part is that there are duplicate records which have more than one discount applied, often with a discount amount and a discount percentage. In these cases, the "first" record's discount is straightforward, but the second discount record's discount amount needs to be factored on the "new" net total. Each record has a numbered field that determines the order in which the discount is applied.

So ID1 has two records, one with a $5 discount and a second with a 10% discount. Based on their respective discount order field values (1 & 2), the $5 discount is applied first and the 10% discount is applied second. Since ID1 has an original value of $100, the first record's discount amount is $(5) and the second record's discount is $(9.50). In the even their discount order field values were reversed, the first record's discount amount would be $(5) and the second record's discount would be $(10).

Additionally, there may be cases with more than two discounts, and the discount order field values do not always begin with 1 (however, discounts are always applied from least order value to greatest).

I've attached an example workbook for clarity and really appreciate any direction that anyone can offer.
Example.xlsx

The forum is fine. I can't relate your narrative to the workbook you posted.

So ID1 has two records, ...

ID1 has 20,000 records

... one with a $5 discount and a second with a 10% discount.

Where do those numbers appear?

Based on their respective discount order field values (1 & 2)

(1&2)? What columns are those?

Since ID1 has an original value of $100 ...

Where does that appear?

Try this in H2 and copied/dragged down:

Formula:

Please Login or Register  to view this content.

This assumes that the order of discount is already in your data set, ie, an earlier discount order will appear in your list before the later discount order for the same ID.

Noticed some errors in testing - ignore the formula originally posted and try this one instead:

Formula:

Please Login or Register  to view this content.