Article updated on August 28, 2017
Question: How do I divide values equally into groups (3 lists or less)?

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:

Categorize values into multiple columns [VBA]

I am fairly new to vba and I am amazed of how much you can automate in excel. In this […]

and this post shows you how to categorize values to columns based on a condition:

Categorize values into multiple columns (excel formulas)

In a previous post I created unqiue distinct column headers (A20:C20) and then categorized adjacent cell values into each column […]

How to enter an array formula

  1. Select cell range C2:G11
  2. Paste above array formula to your formula bar
  3. Press and hold CTRL and SHIFT keys simultaneously
  4. 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.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

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)
            result(ro, co) = ""
        End If
        i = i + 1
    Next co
Next ro
GroupValues = result
End Function

Recommended article:

Split data across multiple sheets [VBA]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

How do I copy the code to my workbook?

  1. Open VB editor (Alt+F11)
  2. Insert a module to your workbook
  3. Paste code to code module
  4. 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

Sort cell values into categories

Here are the prerequisites. Column A contains dates, column B are categories and column C are values. The goal with […]

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:

=IF(D1>0, INDEX($A$2:$A$21, (ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21))+1)*D1-(D1-1)), "")

Array formula in D4:D25:

=IF(D1>1, INDEX($A$2:$A$21, (ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21))+1)*D1-(D1-2)), "")

Array formula in D4:D25:

=IF(D1>2, INDEX($A$2:$A$21, (ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21))+1)*D1-(D1-3)), "")

Try changing value (1, 2 or 3) in D1 and see how List1, List2 and List3 changes.

Recommended article:

Excel: Using lookup to categorize numbers

Problem: Values between 0 and 4 returns Small. Values between 5 and 9 returns Medium. Values between 10 and 15 […]

Download example workbook

Download excel sample file for this tutorial.
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Returns the rownumber of a reference

Returns the smallest number in a set of values. Ignores logical values and text