algorithm for addition

[kaipan]

Hi,

I would like to ask you for help with macro for following problem:

Have an excel table with following data:
- ID
- number of bottles
- number of bottle crates (there are 20 bottles in one one bottle crate)


20168819 4000 bottles
20168819 200 crates
20168838 12000 bottles
20168838 600 crates
20168839 6400 bottles
20168839 320 crates
20168880 9000 bottles
20168880 600 bottles
20168880 480 crates
20168882 12800 bottles
20168882 640 crates
20168883 10400 bottles
20168883 520 crates
20168884 4000 bottles
20168884 200 crates
20168885 7200 bottles
20168885 360 crates
20168885 100 crates

And I need to write a macro which will do this operation for each ID:

(bottles/20)-crates = x

and if "x" is not 0 then write down the value of "x".

There are two points I would like to point out:
- One ID may contain 3 or more rows (see 20168880)
- The macro will work with hundreds IDs so the algorithm should be fast (but it is not necessary)

example:

ID 20168819:
4000/20 - 200 = 0 (...OK)

ID 20168885:
7200/20 - (360+100) = -100

I hope you understand me :-)

Any suggestions?

[DonkeyOte]

Not sure you need VBA for this -- native functions are generally quicker.

If you ensure your first row is blank then given following assumptions:

-- all IDs have min of 2 rows and max of 3 rows
-- if 3 rows 2 could be bottles or 2 could be crates
-- crate always appears in last row for given ID

Please Login or Register  to view this content.

To have 0 display as OK use a Custom Format rather than double evaluate (for speed), apply custom format to D3 onwards of: #;[red](#);"OK"
(obviously change format to suit own preferences re: positive/negative values)

See attached for working example.

[kaipan]

hi DonkeyOte,

thank you. it took my quite a long time to understand your formula :-) I am not as advanced in excel as you.

unfortunatelly, I can't use it. the reason is that one ID may consist from 1 to 10 rows... and I would also like to have it as a macro code because it is a part of a larger macro code..

to make it easier:

I just need to find out
- the total amount of bottles for each ID
- the total amount of crates for each ID

thanks

[kaipan]

I have resolved the matter already, but thank you for your help anyway!