How to Split Data into Equal Groups in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

We may face some situations while dealing with Microsoft Excel where all our data are in a column. But we need to split them into several equal groups. After splitting, data looks more attractive. In this article, we will discuss 3 methods on how to split data into equal groups in Excel with suitable examples and proper illustrations

Table of Contents hide

Download Practice Workbook

3 Methods to Split Data into Equal Groups in Excel

1. Combine INDEX, COLUMNS, ROWS, and IF Functions to Split Data into Equal Groups

2. Split a Long List into Multiple Equal Groups with Excel VBA

3. Use a Custom VBA Function to Split Data into Even Groups

How to Split Data and Tag with Group Numbers

1. Split Data Using the Combination of MAX, ROUNDUP, and PERCENTRANK Functions

2. Combination of ROUNDUP, ROW, and COUNT Functions to Split Data

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Split Data into Equal Groups.xlsm

3 Methods to Split Data into Equal Groups in Excel

We can split data in different ways. We can apply conditions to split data i.e. how many groups or how many cells each group contains. To show all the methods, we will use the following dataset.

1. Combine INDEX, COLUMNS, ROWS, and IF Functions to Split Data into Equal Groups

The IF function is one of the widely used functions of Microsoft Excel. It is a logical function that checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

The INDEX function returns a value or reference of the cell at the intersection of a particular row and a column in a given range.

The COLUMNS function returns the number of columns in an array or reference.

The ROWS function returns the number of rows in a reference or array.

We will make a formula combining these 4 functions to split data.

Step 1:

We will split the given data into 3 equal groups. So, we have added the No. of Groups cell in the dataset and put 3 in the corresponding cell.

Step 2:

Now, go to Cell D7.
Put the following formula on that cell.

=IFERROR(IF($F$4>=COLUMNS($B$5:B5),INDEX($B$5:$B$16,(ROWS($B$5:B5))*$F$4-($F$4-COLUMNS($B$5:B5))),""),"")

Step 3:

Press Enter and drag the Fill Handle icon towards the right side all the way.

We expand the formula toward the right side. Because our primary aim is to split data into groups first. Based on groups and the number of available data, cells will be added to the groups.

Step 4:

Now, drag the Fill Handle icon downwards to complete the splitting.

All data are split equally into different groups.

Formula Breakdown:

Here we are trying to get the 1st input in the 1st group (out of 3 groups). The groups are decorated in a column-like arrangement.

$F$4>=COLUMNS($B$5:B5)

The IF function first checks if the given condition is met. If the criteria are met, the later part of the formula will work, otherwise the IF function will return “”.
Result: TRUE

$F$4-COLUMNS($B$5:B5)

This piece of formula subtracts the number of columns in the given range from the number of total groups.
Result: 2

(ROWS($B$5:B5))*$F$4

Here, the number of rows in the given range is multiplied by the total number of groups.
Result: 3

(ROWS($B$5:B5))*$F$4-($F$4-COLUMNS($B$5:B5))

This formula generates a row number which we will use as an argument for the INDEX function.
Result: 1

INDEX($B$5:$B$16,1)

We get an output here from the given range as specified by the row number.
Result: “Jessica”

IFERROR(IF($F$4>=COLUMNS($B$5:B5),INDEX($B$5:$B$16,(ROWS($B$5:B5))*$F$4-($F$4-COLUMNS($B$5:B5))),””),””)

The IFERROR function checks if there are any errors. If no error is found, then provide the output of the IF function.
Result: “Jessica”

2. Split a Long List into Multiple Equal Groups with Excel VBA

We have a long list containing 12 cells. We want to split them into 3 groups with 4 cells each. Follow the steps below based on Excel VBA code.

Step 1:

First, go to the Sheet Name section at the bottom border of each sheet. Then, press the right button of the mouse.
Choose the View Code option from the list.

Step 2:

Now, the VBA application window will appear. Choose the Module option from the Insert tab.

VBA command module window appears now. We write VBA code on this window and run from here.

Step 3:

Copy and paste the following VBA code on the command module.

Sub split_data_into_equal_groups()
    Dim input_1 As Range
    Dim output_1 As Range
    Dim text_1 As String
    Dim out_array_1 As Variant
    Dim n, m As Long
    On Error Resume Next
    text_1 = ActiveWindow.RangeSelection.Address
Sel:
    Set input_1 = Nothing
    Set input_1 = Application.InputBox("Select input range:", "Range", text_1, , , , , 8)
    If input_1 Is Nothing Then Exit Sub
    If input_1.Areas.Count > 1 Then
        MsgBox "Multiple selections are not supported, select again", vbInformation, "Range"
        GoTo Sel
    End If
    If input_1.Columns.Count > 1 Then
        MsgBox "Multiple selections are not supported, select again", vbInformation, "Range"
        GoTo Sel
    End If
    Set output_1 = Application.InputBox("Select a cell to view the output:", "Start Range", , , , , , 8)
    If output_1 Is Nothing Then Exit Sub
    n = Application.InputBox("Number of cells per column:", "Cell Number", , , , , , 1)
    If n < 1 Then
        MsgBox "incorrect enter", vbInformation, "Cell Number"
        Exit Sub
    End If
    ReDim out_array_1(1 To n, 1 To Int(input_1.Rows.Count / n) + 1)
    For m = 0 To input_1.Rows.Count - 1
      out_array_1(1 + (m Mod n), 1 + Int(m / n)) = input_1.Cells(m + 1)
    Next
    output_1.Range("A1").Resize(n, UBound(out_array_1, 2)) = out_array_1
End Sub

Step 4:

Press F5 to run the code. A dialog box will appear to choose the range.
We choose Range B5:B16 as the input.

Step 5:

2nd dialog appears. This box requires a cell reference to start the output data. We select Cell D5 Then, press OK.

Step 6:

3rd dialog box view now. It indicates how many cells per group contains. We put 4 here.

Step 7:

Finally, press OK.

Our data is split into 3 groups equally. We need to mention how many cells per group contains when running the code.

Similar Readings

3. Use a Custom VBA Function to Split Data into Even Groups

In this section, we will form a custom VBA Function to split data into equal groups.

Step 1:

Press Alt+F11 to enter the VBA command module.
Put the following VBA code on the command module and save the code.

Function GroupValues(range_1 As Range)
Dim output As Variant
n = Application.Caller.Columns.Count
m = Application.Caller.Rows.Count
ReDim output(1 To m, 1 To n)
k = 1
For row_1 = 1 To m
    For column_1 = 1 To n
        If range_1.Cells(k) <> "" Then
            output(row_1, column_1) = range_1.Cells(k)
        Else
            output(row_1, column_1) = ""
        End If
        k = k + 1
    Next column_1
Next row_1
GroupValues = output
End Function

Step 2:

Go to the dataset. Choose Range D5:F8, where data will turn into groups.
Now, go to the formula bar and put the following formula.

=GroupValues(B5:B16)

Step 3:

This is an array formula. So, press Ctrl + Shift + Enter.

We can see that data of a long list transform into equal groups. This GroupValues is a custom VBA function.

How to Split Data and Tag with Group Numbers

In the previous section, we split data into groups equally. But in this section, we will just tag each data with group numbers, and will not divide them into groups. Also, mention that we will apply the below methods only for numeric data.

1. Split Data Using the Combination of MAX, ROUNDUP, and PERCENTRANK Functions

The MAX function returns the largest value in a set of values. Ignores logical values and texts.

The ROUNDUP function rounds a number up, away from 0.

The PERCENTRANK function returns the rank of a value in a dataset as the percentage of the dataset.

We combine the MAX, ROUNDUP, and PERCENTRANK functions to split data. But this combination is applicable only for numeric data.

We will consider the dataset below for operation.

Step 1:

Go to Cell C5.
Put the following formula.

=MAX( ROUNDUP( PERCENTRANK($B$5:$B$12,B5) *4, 0),1)

Step 2:

Now, press the Enter button.

Here, we can see that the given data are tagged with group numbers instead of splitting them. Data are marked with a corresponding group number in the adjacent cell.

Formula Breakdown:

PERCENTRANK($B$5:$B$12,B5)

It calculates the percentage of Cell B5 comparing the Range B5:B12.
Result: 0.142

PERCENTRANK($B$5:$B$12,B5) *4

Multiply the result by 4. This multiplication argument decides how many groups form.
Result: 0.568

ROUNDUP( PERCENTRANK($B$5:$B$12,B5) *4, 0)

This rounds up the result up to 0 digits in the decimal section.
Result: 1

MAX( ROUNDUP( PERCENTRANK($B$5:$B$12,B5) *4, 0),1)

This provides the biggest number based on the roundup result.
Result: 1

2. Combination of ROUNDUP, ROW, and COUNT Functions to Split Data

The ROW function returns the row number of a reference.

The COUNT function counts the number of cells in a range that contains numbers.

We will combine the ROUNDUP, ROW, and COUNT functions to get the corresponding group number of each numeric data.

Step 1:

Go to Cell C5 and put the following formula.

=ROUNDUP(ROW()/(COUNT(B:B)/1),0)

Step 2:

Press the Enter button to execute the formula.

We can see that each data carry the corresponding group number with them. Data will not split applying this method.

Formula Breakdown:

COUNT(B:B)

This counts the number of cells containing numeric data of Column B.
Result: 8

COUNT(B:B)/1

Divide the previous result by 1.
Result: 8

ROW()

Count the number of rows containing data.
Result: 8

ROW()/(COUNT(B:B)/1)

Divide the result of the ROW function by the result of the COUNT function.
Result: 1

ROUNDUP(ROW()/(COUNT(B:B)/1),0)

Rounds up the previous result.
Result: 1

Conclusion

In this article, we described how to split data into equal groups in Excel. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.

2 Comments

Dylan Mar 21, 2023 at 3:47 AM

Good afternoon,

I am looking for help to take this equation one step further. I have a value assigned to each member. I am trying to figure out how to create a formula that will split them into equal groups while making sure the total value of each group is within 1. Any help would be greatly appreciated.

Thank you
- Reply
  Lutfor Rahman Shimanto Mar 25, 2023 at 10:58 AM
  Hello DYLAN,
  
  Thank you for reaching out to us on our website. I understand that you have a value assigned to each member and are trying to split them into equal groups while ensuring each group’s total weight is within 1. Based on your requirements, I can assist you with this by applying an Excel VBA code.
```
Sub SplitIntoGroups()
Dim lastRow As Integer, totalValue As Double, group As Integer
Dim assignedValue As Double
Dim groupTotal As Double, i As Integer
Dim uniqueValues As Variant

lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
groupTotal = 0
group = 1

For i = 2 To lastRow
    assignedValue = Cells(i, 2).value
    If groupTotal + assignedValue <= 1 Then
        groupTotal = groupTotal + assignedValue
        Cells(i, 3).value = "Group " & group
    Else
        group = group + 1
        Cells(i, 3).value = "Group " & group
        groupTotal = assignedValue
    End If
Next i

uniqueValues = GetUniqueValues()
Range("E1").value = "Unique Group"
Range("F1").value = "Total Weighted Value"
End Sub

Function GetUniqueValues() As Variant
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim uniqueValues As Variant
    
    Range("E:E").ClearContents
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    uniqueValues = Application.WorksheetFunction.Unique(ws.Range("C2:C" & lastRow))
    ws.Cells(2, 5).Resize(UBound(uniqueValues), 1).value = uniqueValues
    
    lastRow = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row

    Range("F:F").ClearContents
    Range("F2").Formula = "=SUMIFS($B:$B,$C:$C,$E2)"
    Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)

    GetUniqueValues = uniqueValues
End Function 
```
  The above VBA code splits a list of items into groups based on their assigned value and calculates the total weighted value for each unique group. It loops through each row of the worksheet, sets each item to a group based on the total weighted value of the group, and creates a new group if the current group's total weighted value plus the contemporary item's assigned value is greater than 1. The GetUniqueValues function calculates the unique groups and their total weighted values by using the Unique and SUMIFS functions to generate an array of unique values in column C and calculate the total weighted value for each group.
  
  1) Inserting Required Values:
  
  2) Displaying Final Output:
  
  3) Download Practice Workbook
  https://www.exceldemy.com/wp-content/uploads/2023/03/Dylans-Request.xlsm
  
  Thank you again for reaching out to us, and I look forward to hearing from you soon.
  
  Best regards,
  Lutfor Rahman Shimanto

How to Split Data into Equal Groups in Excel (3 Methods)

Download Practice Workbook

3 Methods to Split Data into Equal Groups in Excel

1. Combine INDEX, COLUMNS, ROWS, and IF Functions to Split Data into Equal Groups

2. Split a Long List into Multiple Equal Groups with Excel VBA

3. Use a Custom VBA Function to Split Data into Even Groups

How to Split Data and Tag with Group Numbers

1. Split Data Using the Combination of MAX, ROUNDUP, and PERCENTRANK Functions

2. Combination of ROUNDUP, ROW, and COUNT Functions to Split Data

Conclusion

Related Articles

Alok Paul

How to Make a Schedule for Employees in Excel (3 Types)

How to Count Number of Columns in Excel (5 Suitable Examples)

How to Make an Availability Schedule in Excel (with Easy Steps)

Excel Reference Named Range in Another Sheet (3 Examples)

Leave a reply Cancel reply