Allocating Customer Numbers to Different Companies (Sums)

[davm22]

Apologies for the title as it is quite hard to describe.


I am looking for a solution to my problem (either simply Excel or Macro/Database based) to do the following:

- The list of customers must be allocated to different companies. They must be kept as whole as possible and preferably in order. Please see the attached Excel file for an example.

On the attachment Customer 1 has 60 (fig.1) and this has to be allocated to the different companies. Company A requires 50 (fig.2). This 50 can be allocated from Customer 1 (fig.3). Customer 1 has 10 remaining so this passes on to Company B which requires 100. The 100 can be made up of Customer 1 (10), Customer 2 (8), Customer 3 (5) and partially of Customer 4 (77 with 33 remaining to Company C).

Thanks for reading this thread. Can anyone provide any ideas on how to do this more efficiently as at the moment it is very time consuming as the lists of 'Customers' and 'Companies' can grow to be quite large.

Thanks again

[davm22]

Is anyone able to help with this?

[Pete_UK]

I'm looking at putting the names and companies in a table - I'll get back to you when I've finished.

Hope this helps.

Pete

[Pete_UK]

I've added a table to your file, showing how the values can be allocated (and displayed).

Hope this helps.

Pete

[davm22]

I've added a table to your file, showing how the values can be allocated (and displayed).

Hope this helps.

Pete

That looks great! Thanks a lot for your help.

Now I'll have a look and decipher the formula for larger examples, thanks again

[Pete_UK]

Now I'll have a look and decipher the formula for larger examples, thanks again

Note that for larger tables, this formula is in the first cell (E30 in the example):

Please Login or Register  to view this content.

This formula is in the next cell (F30) and is copied across that row only:

Please Login or Register  to view this content.

The following formula is in cell E31, and is copied down that column only:

Please Login or Register  to view this content.

This formula is in cell F31:

Please Login or Register  to view this content.

and this is copied across and down all the other cells as required to suit the size of table that you have. I've also applied conditional formatting to the cells so that zero appears blank (i.e. if cell content is zero, use white foreground colour).

Hope this helps.

Pete

[davm22]

Thanks again Pete, I have it working now so that is brilliant.

I have my large table now and I am working through rearranging it so that I can see clearly all of the amounts in the columns e.g. Just for Company A.

Would there be a quick way to bring in the information so that it shows:

Company A
Customer 1 50

Company B
Customer 1 10
Customer 2 8
Customer 3 5
Customer 4 77

etc.

So it doesnt bring in 0 cells. Is the best way to do that this formula:

=IF($E30>0,E30,"")

If you aren't able to help, thanks again for your help on this

[Pete_UK]

It has been very difficult trying to reply to you today, but hopefully the Forum will remain stable enough for me to get through this time.

I've attached an amended version of the file I posted earlier, and in this I have shown one way of achieving what you want. Essentially, I've listed all combinations of Companies and Customers, but by applying the filter for not equal to zero you can hide the rows with zero amounts on them. Just re-apply the filter if the data changes.

Hope this helps.

Pete