This post shows you two different approaches, a User Defined Function and an array formula. So which one should you use, I recommend using the UDF.
It is easier to use in this case while the array formula needs a little tweaking to get the right number of groups.
User defined function
You decide how many groups you want by selecting a cell range with as many columns as you want groups and then enter the UDF. It is designed to group values depending on how many columns you have selected before entering it.
The animated picture above shows you a cell range with 5 columns.
This post shows you how to categorize values into worksheets:
[insertexcerpt id=โ3846″]
and this post shows you how to categorize values to columns based on a condition:
[insertexcerpt id=โ3857″]
How to enter an array formula
 Select cell range C2:G11

Paste above array formula to your formula bar
 Press and hold CTRL and SHIFT keys simultaneously
 Press Enter once
The formula in the formula bar now looks like this: {=GroupValues(A2:A24)}
Donโt enter these curly parentheses yourself, they appear automatically if you did the above steps correctly.
[insertexcerpt id=โ23258″]
VBA code
Function GroupValues(rng As Range) Dim result As Variant c = Application.Caller.Columns.Count r = Application.Caller.Rows.Count ReDim result(1 To r, 1 To c) i = 1 For ro = 1 To r For co = 1 To c If rng.Cells(i) <> "" Then result(ro, co) = rng.Cells(i) Else result(ro, co) = "" End If i = i + 1 Next co Next ro GroupValues = result End Function
Recommended article:
[insertexcerpt id=โ3805″]
How do I copy the code to my workbook?
 Open VB editor (Alt+F11)
 Insert a module to your workbook

Paste code to code module
 Go back to Excel
Note, make sure you save your workbook as a *.xlsm file or you will lose all your vba code.
Download excel *.xlsm file
Group values.xlsm
Recommended article
[insertexcerpt id=โ1040″]
Array formula
The following picture shows you values in column A and they are equally split across three columns, column C, D and E.
Array formula in C4:C25:
Array formula in D4:D25:
Array formula in D4:D25:
Try changing value (1, 2 or 3) in D1 and see how List1, List2 and List3 changes.
Recommended article:
[insertexcerpt id=โ378″]
Download example workbook
Download excel sample file for this tutorial.
splitvaluesintogroupsusingexcelformula.xls
(Excel 972003 Workbook *.xls)
Functions in this article:
IF(logical_test;[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
ROW(reference)
Returns the rownumber of a reference
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
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(D14)),โโ)
the value at f4 is DD but the f5, f6,etcโฆ.arejust repeating the result
Reply
Fahmy,
splitvaluesintogroupsusingexcelformula.xlsx
Reply
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!
Thanks!
Reply
Hasan,
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.
Hasan1.xlsx
Reply
HI Oscar! I would need your help to strech column A to 500 rows with 8 lists. Thanks!
Reply
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
Reply
Kerien
Here is a workbook for you:
splitvaluesintogroupsusingexcelformulav2.xlsx
Reply
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โฆ
Link
Cheers!
Reply
Joe,
I have built a user defined function that is easier to use, read this post again.
Reply
Hi Oscar, Thank you so much, this is much easier, thankyou!
Reply
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!
Reply
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.
Reply
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.
Reply