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.
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 + i + 1).PasteSpecial xlAll
.Cells(R + i + 1, iColCandidates).Value = strSplit(i)
.Cells(R + i + 1, iColDonation).Value = iDonation
Application.ScreenUpdating = True ' This code speeds up the process
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.