+ Reply to Thread
Results 1 to 6 of 6

Sorting a cell amount into specific labelled amount columns

  1. #1
    Registered User
    Join Date
    09-28-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    4

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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.
    Last edited by DonkeyOte; 09-29-2010 at 07:19 AM. Reason: typo in narrative

  3. #3
    Registered User
    Join Date
    09-28-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    4

    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.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-28-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sorting a cell amount into specific labelled amount columns

    Quote Originally Posted by DonkeyOte View Post
    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.
    Attached Files Attached Files

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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:

    Quote 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)
    Last edited by DonkeyOte; 09-30-2010 at 04:40 AM.

  6. #6
    Registered User
    Join Date
    09-28-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    4

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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