13 responses

  1. Fahmy
    May 7, 2012

    i tried to add a list number 4 with this formula =IF(D1>3,INDEX($A$2:$A$21,(ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21))+1)*D1-(D1-4)),””)
    the value at f4 is DD but the f5, f6,etc….arejust repeating the result


  2. Oscar
    May 11, 2012
  3. Hasan
    August 3, 2012

    Hi Oscar, thanks for the great tutorial.

    I was hoping to use what you’ve described above but for data that stretches across a single row.

    For example, if I have data that stretches from A1:J1, I would like to split it up into 5 rows so that I would have the values appear in A2:B2, A3:B3, A4:B4,A5:B5, A6:B6.

    I have taken a stab at manipulating the formulas you’ve provided but with no luck. Was hoping you could help out!



    • Oscar
      August 6, 2012


      Array formula in cell range: A2:E3:


      How to create array formula
      1. Select cell range A2:E3
      2. Paste formula in formula bar
      3. Press and hold Ctrl + Shift
      4. Press Enter

      Adjust bolded cell ranges if you enter the array formula in a different cell range.



  4. Chris
    February 24, 2016

    HI Oscar! I would need your help to strech column A to 500 rows with 8 lists. Thanks!


  5. Kerien
    March 16, 2017

    Hey, quick question, would this beable to expand into say a list of 100 values and have 10 groups. ive tried editing the code but am unsuccessful in getting the formula to work correctly


  6. Joe
    April 20, 2017

    Hi Oscar, I wonder if you could help, I have a large number of record numbers (basically a call list for a sales office) which I need to split up into equal lists, there could be between 100 and about 400 items.
    I have tried to use your example spreadsheet and replace the ranges with dynamic named ranges (which are set up contain all the record numbers), but that gives me a #VALUE error, even if I use crtl+alt+enter.
    In addition, I cannot seem to make the lists in your example longer to accommodate this, if I copy and paste the formula downward, I get one record number repeated.

    Here is a link to my spreadsheet on google drive (I am using excel 2013 – just using google drive to host the file!) I would really appreciate it if you could have a look and let me know where I’m going wrong…



    • Oscar
      May 3, 2017


      I have built a user defined function that is easier to use, read this post again.


  7. Joe
    May 16, 2017

    Hi Oscar, Thank you so much, this is much easier, thankyou!


  8. Thomas Han
    May 18, 2017

    Hi Oscar! Thank you so much for this tutorial. I have been trying to replicate this exact workbook for 10 groups, and also use the “CHOOSE” AND “RANDBETWEEN” functions to randomize the values of column A into the array but having a bit of trouble doing so. Would this be possible to set this up? Really appreciate your help!


  9. Tamara Smith
    September 26, 2017

    I am trying to use your formula (downloaded the spreadsheet) to create divide 253 students (value) into 15 groups equally. I tried to edit the code…but i’m getting an error message when trying to adjust the array.


    • Oscar
      September 29, 2017

      Tamara Smith,

      If you are trying to expand the array formula and get an error, try this:

      The array formula in column C is entered in cell range C4:C25.
      Select cell your new cell range, example C4:C27
      Click in formula bar.
      Press and hold CTRL + SHIFT. Press Enter to create a new array formula for your new range.

      I recommend you use the User Defined Function, it is much easier to work with.


Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top
mobile desktop