[Urgent] how to net a value with a list of value?

[lok1234]

Dear all,

I am stuck over with the question below , please kindly help.. thank you in advance.


I have the following table with sample data as below:

Date, Fund, Unit, Clear?
1-Mar, A, 3.1, N
2-Mar, A, 2.2, N
3-Mar, A, 4.7, N

Each day, user will input the "Unit of Fund A" to this table.

Before inserting, this inputted unit has to be netted the unit that already in the table.

Say, for example:

In 4-Mar, user input -7 as the unit

This -7 unit would be netted the unit in 1-Mar (i.e. 3.1 unit)
then, -3.9 unit would be remained.

This -3.9 unit would be further netted the unit in 2-Mar (i.e. 2.2 unit)
then, -1.7 unit would be remained.

This -1.7 unit would be further netted the unit in 3-Mar (i.e. 4.7 unit)
then, +3 unit will be remained on 4-Mar

This +3 unit will be inserted into the above table and since all the records that before 4-Mar have been netted off,
they are "Cleared".

Finally, the table would look like this:

Date, Fund, Unit, Clear?
1-Mar, A, 3.1, Y
2-Mar, A, 2.2, Y
3-Mar, A, 4.7, Y
4-Mar, A, 3.0, N


I supposed I have to upload all the "unclear" record from the table into an array in the first state. Then, I have to compare the inputted value with the "unit" columns of the array.

But how? any functions in VB can help me to perform this comparsion?

Thank you.

(P.S. the above values are just examples, in realistic, the unit in the table and the inputted value can be both -ve and +ve)