View original (French)

Randomly distribute in Excel

Solved
Tibo. -  
gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   -
Hello,

After several unsuccessful searches on the internet, I am turning to you for an answer. Maybe I didn't find a response because I didn't know exactly what keywords to type. Anyway, here is my problem, which seemed simple but actually is not...

I have a list of 200 items that I want to distribute randomly across 22 stock zones.

I thought about generating random numbers between 1 and 22. But actually, I would like the 22 zones to contain almost the same number of items.

Um... If I'm not clear, I can rephrase, or at least try.

Thank you very much for your help!

Tibo.

Configuration: Windows 7 / Firefox 10.0.2

6 answers

  1. Tibo.
     
    I didn't ask my question in the right place?
    0
  2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello

    here is my problem, which seemed quite simple, but actually isn't...

    Indeed, you are right, the solution to your problem is not so easy to find and you shouldn't be surprised that the answers didn't come flooding in.

    To resolve your issue, I've made a small example for you:

    https://www.cjoint.com/?BBvwEV6DyMw

    If you don't have all the keys to use it, feel free to ask questions.

    Always stay calm.
    0
    1. Raymond PENTIER Posted messages 58226 Registration date   Status Contributor Last intervention   17 490
       
      Impressive!
      0
    2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello Raymond,

      Thank you for the message but we need to wait to see if it meets Tibo's expectations.
      0
  3. Tibo.
     
    Hi gbinforme!

    Indeed, it's impressive! And indeed, it wasn't straightforward. So a big thank you for your solution!

    However, if I understand your work correctly, the articles within each area do succeed each other, right?
    And why are some articles repeated twice?

    In any case, thank you very much!
    0
    1. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello Tibo,

      I based myself on your request: that the 22 zones contain almost the same number of articles.

      Why are some articles repeated twice?

      Simply because you have 200 articles to fit into 22 zones:
      - either I put 9 per zone and you have 2 left over.
      - or I put 10 per zone (like in the example) and you have 18 duplicates with 220 articles fitted. It's just basic arithmetic.

      The articles within each zone follow one another, right?

      With the formula I gave you, that's the case because the zones are filled successively to ensure a good distribution.

      If you want to have a more eclectic mix, you need to introduce an additional factor into the formula that I haven't worked on because it needs to be properly accounted for due to the very dissimilar numbers of zones and articles. If that is of interest to you and your 22 zones and 200 articles are fixed, I'm willing to take a look.
      0
  4. JvDo Posted messages 1924 Registration date   Status Member Last intervention   859
     
    Good evening everyone,

    If you want randomness in sorting, without repetition, you can consider, based on the proposal from gbinforme:
    1) generating a random sequence in $C$2:$C$201 using the formula =RAND()
    2) then, in the stocks area, use the formula:
    =IF((ROW()-ROW(stocks))*22+COLUMN()-COLUMN(stocks)+1>nbarticles,"",INDEX($A$2:$A$201,RANK(INDEX($C$2:$C$201,(ROW()-ROW(stocks))*COLUMNS(stocks)+COLUMN()-COLUMN(stocks)+1),$C$2:$C$201,1)))

    I use nbarticles which equals COUNTA(articles).

    I would have liked to hide this "random" column in C:C but I couldn't figure out how to create a name that doesn't rely on cells (like =RAND()*(ROW(INDIRECT("1:"&nbarticles))>=0)) and contains a stable random sequence.

    Best regards.
    0
  6. Tibo.
     
    Wow!

    The gbinforme/JvDo couple is impressive! It's exactly what I was looking for!

    A big thank you to you, O Great Sages of Excel! ;)

    Tibo.

    P.S.: How can you think of writing a formula like that?! Where do we learn this?!
    0
    1. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello Tibo,

      Be careful with the random formula because your sorting gets messed up with each calculation; you need to fix your column C when the ranking suits you (copy/paste special/values).
      0
    2. Tibo.
       
      I knew it, so I took the initiative and completely copied the full table into another file with a special values paste.
      So it works perfectly.

      And I'll be able to play it cool if someone glances at my screen in the office! Well, they better not ask questions...


      Anyway, thanks again!
      0
  7. @moon Posted messages 1 Status Member
     
    Hello,
    I have the same problem as tibo but it's more complicated... a number Y of articles to distribute across X points, but respecting certain criteria...
    I wanted to see the example from gbinforme but I can't download the document.. can someone help me please?
    0
    1. Raymond PENTIER Posted messages 58226 Registration date   Status Contributor Last intervention   17 490
       
      Normal: it dates back 27 months, while the cjoint.com server states "CJoint.com keeps your link active for the chosen duration. It is then permanently deleted along with any associated attachments. For unlimited, the link and documents will be deleted if they remain unaccessed for 60 consecutive days."
      0
    2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello,

      My allocation was done using an OFFSET formula 
      =OFFSET(articles;MOD(((COLUMN()-2)*(COUNTA(articles)/COUNTA(stock)))+(ROW()-1);COUNTA(articles));;1;1)

      So the allocation only has the results of the calculations as selection criteria.

      If you could provide us with an example of the data structure, without any personal elements, with the desired results explained clearly, it would be easier to assist you.
      To share your workbook, you can upload it to https://www.cjoint.com/ and post the obtained link here.
      0