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.

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

Application.ScreenUpdating = True' This code speeds up the processEndSub

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.