Home / Macros / Split Row Into Multiple Rows

Split Row Into Multiple Rows

The Excel HelpDesk Support team received the following request for help:

Problem – Need to split rows that hold information with multiple candidates to 1 row per candidate

I have a dataset in Excel that has to do with political campaign contributions.
In Column H (Rec_CandName), there are three names of candidates (Andre Baker, Howard Gardner, Dave Hennessey). In Column AC (Cont_Amt), there is a value which is the dollar amount of the donation to these three candidates.

Basically, I want this dataset to have only one candidate in Column H for each donation, and I want to give that one candidate 1/3 of the total dollar amount given to the three candidates for that donation.

For example, if one row shows that Baker, Gardner and Hennessey (Row H) got $30 (Row AC), I want to create three identical rows that have all the same information as that row except that in the first row, Column H will say Andre Baker and Column AC will say 10. In the second duplicate row, Column H will say Howard Gardner and Column AC will say 10. In the third duplicate row, Column H will say Dave Hennessey and Column AC will say 10.

I do not know how to write code using VBA and I was wondering if you could help. Thank you :)

 

Create a Custom Macro to Split and Calculate

You can use VBA (Visual Basic for Applications) to loop through a number of rows and if the candidates are separated by a comma use a function in VBA that splits the text into however many separate values are found between the comma’s

For this request  we received a sample workbook with data in the following format

 

If you copy the code provided below into VBA editor within the workbook and adjust the column numbers and the delimiter is used (in this case a comma) the code will split each row up into the required number of rows, divide the amount by the number of candidates and delete the original row.

To access the VBA editor you simply hit Alt + F11 and paste the code into a worksheet within your workbook. To save your workbook you would have to save it as an Macro Enabled Excel file.

Option Explicit
 
Sub SplitDonationPerCandidate()
 
Dim R                   As Long
Dim Rx                  As Long
Dim iColCandidates      As Integer
Dim iColDonation        As Integer
Dim strDelimiter        As String
Dim strCandidates       As String
Dim i                   As Integer
Dim strSplit()          As String
Dim iDivide             As Integer
Dim iDonation           As Integer
 
iColCandidates = 8                      'You can change this number 8 = column H
iColDonation = 29                       'You can change this number 29 = column AC
strDelimiter = ","                      'You can use this to whatever sign is used to separate the values

Application.ScreenUpdating = False      'This code speeds up the process

With ThisWorkbook.ActiveSheet           'Make sure you're on the sheet where you want the code to run
    Rx = .UsedRange.Rows.Count
 
    For R = Rx To 2 Step -1
        strCandidates = .Cells(R, iColCandidates).Value
 
        'With the delimiter work out the different candidates
        strSplit = Split(strCandidates, strDelimiter, , vbTextCompare)
 
        'How many candidates are found?
        iDivide = UBound(strSplit) + 1
 
        'Divide the total donation by the number of candidates
        iDonation = .Cells(R, iColDonation).Value / iDivide
 
        'Loop through the range with candidates and copy the rows per candidate and change the candidate and the donation amount
        For i = 0 To UBound(strSplit)
            .Rows(R + i + 1).Insert
            .Rows(R).Copy
            .Rows(R + i + 1).PasteSpecial xlAll
            .Cells(R + i + 1, iColCandidates).Value = strSplit(i)
            .Cells(R + i + 1, iColDonation).Value = iDonation
        Next
        .Rows(R).Delete
    Next
End With
 
Application.ScreenUpdating = True       ' This code speeds up the process
End Sub

 

Running the Custom Routine

Once you have copied the VBA code into your workbook, make sure you have selected the worksheet that has the data that you want to split and calculate and then select any line of code within the macro routine and then hit F5.

The macro will run and the rows will be split and calculated values returned. The result should look like the following

 

You can customise the code for other data by simply changing the column numbers where you have the data you want to split and where the data is that needs dividing

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk

Check Also

Transfer most Recent Records

A macro developed to compare rows of data and then transfer unique rows from the source worksheet into a result worksheet. This can be applied across many different uses in other spreadsheets.