## Split values equally into groups

*Article last updated on April 12, 2018*

**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**

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

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) Else 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?**

- 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

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:

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:

**Download example workbook**

Download excel sample file for this tutorial.

split-values-into-groups-using-excel-formula.xls

(Excel 97-2003 Workbook *.xls)

This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The […]

Working with COMBO BOXES [Form Controls]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Change chart data range using a Drop Down List [VBA]

This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) […]

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 […]

Text to columns: Split words in a cell [Array formula]

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

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 […]

### 13 Responses to “Split values equally into groups”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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

Fahmy,

split-values-into-groups-using-excel-formula.xlsx

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!

Hasan,

Array formula in cell range: A2:E3:

A2:E3))+1)+(ROWS(A2:E3)*(COLUMN()-1))),"")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

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

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

Kerien

Here is a workbook for you:

split-values-into-groups-using-excel-formulav2.xlsx

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!

Joe,

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

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

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!

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.

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.