Fill in groups up to a specific value based on second column

[SkomantasTamulaitis]

Hello, I am working with the table below, file attached to this comment. 

The table has 2 fields, Animal ID and Animal Group. There are 40 animals and 10 groups, each group should have 4 animals in it. The tricky part is that I want the animals with the lowest Animal ID to be added to the group with the lowest Animal Group value, but groups are missing different numbers of animals to be filled.

In this example group 1 is missing 1 animal to reach 4, so  I want Animal ID 2 to go to Group 1. Group 2 only has 2 members so it needs 2 more, so Animal ID 7 and  Animal ID 10 would go to Group 2. Is it possible to solve it without an iterative macro?

[IraWatt · Accepted solution]

Hey @SkomantasTamulaitis,

This seems to do the job:

You will probably want to check over the numbers.

Any questions or issues please ask :)
HTH!
Ira

[SkomantasTamulaitis]

Fantastic, thanks for such a prompt response!

[IraWatt]

No worries @SkomantasTamulaitis :)