Batching into monetary groups

[katieE1885]

Every month I need to split a huge batch of payments into groups of £50k - is there an easy way of doing this rather than having to manually figure it out ? Thanks

[#2]

@katieE1885 Can you give an example of what you mean by "split into groups of £50k" ?

[#3]

Thanks - so for example we have a list of say 200 suppliers all due to be paid different amounts say totalling £1million but we can only pay in three batches of £50000 a day. At present we are having to manually add /try and find random batches that add up to £50000. I’m hoping there is a formula or a macro or alike that I can apply to the listing of £1m which I’ll automatically split out into batches of £50000 saving hours of trying to add them up to make the £50k… hope that’s a bit clearer for you thanks

[#4]

@katieE1885 If you can live with batches that may not exceed 50000, than perhaps the attached workbook may help. I created a list of 200 random amounts. Using the MOD function to determine when the running total exceeds 50000 and an IF to assign a batch number.

[#5]

That it absolutely amazing and so so kind of you thank you I really appreciate it

[#6]

@katieE1885 Ooops! don't think so as I believe I made a mistake.

[#7]

It certainly looks like it achieves what I am after

[#8]

@katieE1885 No it does not. Only the first batch is correct. Some others may be correct but that's a coincidence. Just sum the amounts per batch and you will see that some go over 50000. Sorry about that!

[#9]

Ahh ok I only looked at the first batch

[#10]

@katieE1885 I'll see if I can come up with something better. Or perhaps someone else!

[#11]

brilliant thinking.