# Sorting a cell amount into specific labelled amount columns

1. ## Sorting a cell amount into specific labelled amount columns

Hi guys,

I'm a relative newbie to excel, i.e. can do formulas, but not much to do with VBA/macros etc so there may be a method to do it that way but I may need walking through.

A brief overview of what i want this excel sheet to do. I'm doing a project on merchandise suppliers (we have 6), and they have supplied us with prices for various bulk purchases (i.e a pallet of 100, 500, 2000 etc as examples). We can only buy from the bulk prices and not individually so if i require 2350 items, ill need 1 x 2000 and 4 x 100 which will give me 2400 as I cannot have less than what i require.

I want to sort various amounts found in cell A6-A11, into columns labelled from B5-G5 that carry various values.
For example if I have an amount of 2000, and I have columns in order from left to right of 100, 500, 1000 and 1500.
I want it to attempt to sort itself from the highest value to the lowest value. So in that case it will leave a mark or the number "1" to represent the amount of multiples in the column as 1 x 1500, and 1 x 500.

Once I have that designed I need it to deal with slightly more difficult numbers. For example if the number in cell A6 was 2250, and I have columns in order from left to right of 100, 500, 1000 and 1500 as before.

It would sort from the highest amount to lowest in 1 x 1500, 1 x 500, and 2 x 100, but the remainder of 50 would not be allocated.
In this case I need the remainder to be rounded up so that it is counted as another 1 x 100, giving me a total of 2300, from my original 2250 value entered.

Please see my example I have put together. It is not capable however to run the remainders into the COUNT section I have set up.
Any help is appreciated.

2. ## Re: Sorting a cell amount into specific labelled amount columns

As a starting point, based on your sample file & values, how about:

``Please Login or Register  to view this content.``

3. ## Re: Sorting a cell amount into specific labelled amount columns

Thanks DonkeyOte, works a treat!

Next I have to try and see how to see if an amount is cheaper to buy as a bigger bulk amount, or the exact amount required.

I.e I am required to buy 45500 items, i could buy 2 x 20000, 0 x 10000, 1 x 5000 and a 1 x 500 to get my exact amount but based on the prices, am i better off rounding to 50000 items and buying 2 x 20000, and 1 x 10000.

Not sure if you'll understand what im saying or not, but thought id throw it out there! :-P

I have attached an example below.

4. ## Re: Sorting a cell amount into specific labelled amount columns

Originally Posted by DonkeyOte
As a starting point, based on your sample file & values, how about:

``Please Login or Register  to view this content.``
One problem I have encountered with the formula is when it comes to sorting into bulk numbers like 144, and 288 it doesn't work to well.

See attached example.

5. ## Re: Sorting a cell amount into specific labelled amount columns

Using the latest example:

``Please Login or Register  to view this content.``
The above is based on the earlier methodology regards best pricing.

However, based on:

Originally Posted by mcdooglie
I am required to buy 45500 items, i could buy 2 x 20000, 1 x 5000 and a 1 x 500 to get my exact amount but based on the prices, am i better off rounding to 50000 items and buying 2 x 20000, and 1 x 10000.
edit:

it really depends on the pricing - your table in the latest sample implies smaller units are more expensive which would be an odd scenario I think, no ?
we would expect smallest to be cheapest but with marginal price reducing due to economies of scale, eg:

``Please Login or Register  to view this content.``
so 50 item is cheaper than 100 item but were you looking to cater for say 75 it would always be cheaper to buy 1 unit of 100 than 2 units of 50

I've re-written this above five times now ... going around in circles...

I think the marginal reduction in per unit pricing will also have a bearing on the optimal combination, consider:

``Please Login or Register  to view this content.``
If you want to buy 2700 units it would be cheaper to buy 1 x 500 + 1 x 2500 [0.5 + 1.4] than any other combination.

However, if we reduce the margin between prices further such that:

``Please Login or Register  to view this content.``
then the optimal bundle changes to 1 x 5000

I think this might warrant Solver (not something I have any skill with myself)

6. ## Re: Sorting a cell amount into specific labelled amount columns

DonkeyOte you are awesome, thanks for spending the time to develop a solution and reply, greatly appreciated.

I'm researching the Solver now to see if I can develop something, will let you know if its a success :-P

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1