Split values equally into groups
Table of Contents
1. Split values equally into groups
This post shows you two different approaches, an array formula, and a User Defined Function. You will find the UDF later in this post.
The above picture shows you values in column A and they are equally split across 9 columns, column D to M.
Formula in D5:
Now copy cell D5 and paste to cells below and to the right.
1.1 Explaining formula in cell D5
Step - 1 Keep track of lists
The COLUMNS function works just like the ROWS function except it counts columns instead of rows in a cell reference.
Cell | COLUMNS function | Result |
D5 | COLUMNS($A$1:A1) | 1 |
E5 | COLUMNS($A$1:A2) | 2 |
F5 | COLUMNS($A$1:A3) | 3 |
Cell E2 contains the number of lists to put the values into.
$E$2>=COLUMNS($A$1:A1) returns TRUE. This makes the formula return blank cells when the number of lists are greater than the value in cell E2.
Step 2 - Return blanks if all lists are populated
The IF function lets you specify a value if the logical expression returns TRUE (argument 2) and another value if FALSE (argument 3).
IF($E$2>=COLUMNS($A$1:A1),INDEX($B$3:$B$102,(ROWS($A$1:A1))*$E$2-($E$2-COLUMNS($A$1:A1))),"")
returns INDEX($B$3:$B$102,(ROWS($A$1:A1))*$E$2-($E$2-COLUMNS($A$1:A1)))
Step 3 - Return number based on an expanding cell reference
The ROWS function returns the number of rows in a cell reference, this cell ref is special. It expands as the formula is copied to cells below.
ROWS($A$1:A1)
returns 1.
Cell | ROWS function | Result |
B3 | ROWS($A$1:A1) | 1 |
B4 | ROWS($A$1:A2) | 2 |
B5 | ROWS($A$1:A3) | 3 |
Step 4 - Multiply with cell E2
ROWS($A$1:A1)*$E$2 becomes 1*9 equals 9.
Step 5 - Subtract $E$2 with COLUMNS
$E$2-COLUMNS($A$1:A1)) becomes 9-1 equals 8
Step 6 - Calculate row number
ROWS($A$1:A1)*$E$2-($E$2-COLUMNS($A$1:A1)) becomes 9-8 equals 1.
Step 6 - Get value based on row number
The INDEX function returns a value in cell range based on a row and column number. This is a single column cell ref so the column number is not neccessary in this case.
INDEX($B$3:$B$102,(ROWS($A$1:A1))*$E$2-($E$2-COLUMNS($A$1:A1))) returns "YM" in cell D5.
1.2 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.
1.3 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.
Recommended articles
Array formulas allows you to do advanced calculations not possible with regular formulas.
1.4 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
1.5 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
2. Rearrange values based on category - VBA
In this post I am going to rearrange values from a list into unique columns.
Before:
After:
2.1 The code
Sub Categorizedatatocolumns() Dim rng As Range Dim dest As Range Dim vrb As Boolean Dim i As Integer Set rng = Sheets("Sheet1").Range("A4") vrb = False Do While rng <> "" Set dest = Sheets("Sheet1").Range("A20") Do While dest <> "" If rng.Value = dest.Value Then vrb = True End If Set dest = dest.Offset(0, 1) Loop If vrb = False Then dest.Value = rng.Value dest.Font.bold = True End If vrb = False Set rng = rng.Offset(1, 0) Loop Set rng = Sheets("Sheet1").Range("A4") Do While rng <> "" Set dest = Sheets("Sheet1").Range("A20") Do While dest <> "" If rng.Value = dest.Value Then i = 0 Do While dest <> "" Set dest = dest.Offset(1, 0) i = i + 1 Loop Set rng = rng.Offset(0, 1) dest.Value = rng.Value Set rng = rng.Offset(0, -1) Set dest = dest.Offset(-i, 0) End If Set dest = dest.Offset(0, 1) Loop Set rng = rng.Offset(1, 0) Loop End Sub
Get excel tutorial file
Remember to backup your excel workbook, you can´t undo macros.
Categorize-data-into-multiple-columns.xls
(Excel 97-2003Â Workbook *.xls)
Split values category
Table of Contents Split data across multiple sheets - VBA Add values to worksheets based on a condition - VBA […]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]
User defined function category
Table of Contents Search for a file in folder and sub folders - User Defined Function Search for a file […]
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Excel categories
15 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
Paste image link to your comment.
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:
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 (the spreadsheet file) 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
Press with left mouse button 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.
Hello
i really appreciate your blog its very helpful.
however my is issue is i do not want to list the names. i want to know how many PEOPLE should be in each group. so i i have levels and each level has a specific number of people.
https://postimg.cc/nXbTRzpd
and based on the number OF PEOPLE they will be divided equally into N number of groups (1 group, 2 groups , .... up to 8 groups some times)
https://postimg.cc/9DQzjPmz
so i will have the table display horizontal with number of people in each group.
https://postimg.cc/s13Q3GJF
how can i do that?
bu örneği excel 2016 ya göre tek sütunda nasıl yapılır yardımcı olurmu sunuz