Split column add row

[dw700d]

Good day,

I have a spreadsheet with 26,000 rows.

When there are multiple codes in the code column I would like to create a new row and split the spend amounts evenly between each code in the column.

For example, case # 6 has 3 codes. I would like to create a new row for each code and split the dollar amount evenly across all the 3 codes. Thanks in advance for your help

Original

Case  #

Spend

Code

Master Project(s)

Date

6

$900

SB25, RT3SD, JI890

F

1/6/2020

After Splitting Codes

Case  #

Spend

Code

Master Project(s)

Date

6

$300

SB25

F

1/6/2020

6

$300

RT3SD

F

1/6/2020

6

$300

JI890

F

1/6/2020

Data Set

Case  #	Spend	Code	Master Project(s)	Date
1	($853)	T3640	A	1/1/2020
3	$26,185	AK30, A484	C	1/3/2020
4	$184,383	TBLP, UH07	D	1/4/2020
5	$2,192	AK045, D24G	E	1/5/2020
6	$900	SB25, RT3SD, JI890	F	1/6/2020
20	$546	M643, SS00	T	1/20/2020
26	$114,432	UI7, UFR, UE95, UE52	Z	1/26/2020
27	$20,962	UG34, UGFR	AA	1/27/2020
28	$64,689	UG77	AB	1/28/2020
29	$229,271	TBLP, U80, U76	AC	1/29/2020
32	$125,366	TLP, UG1, 0FR, U76	AF	2/1/2020
47	$13,538	AK36, AK04	AU	2/16/2020
48	$3,279	XP17, XR14	AV	2/17/2020
49	$8,970	AK84	AW	2/18/2020

[#2 · Accepted solution]

@dw700d 

Run the following macro:

Sub SplitData()
    Dim r As Long
    Dim m As Long
    Dim a() As String
    Dim i As Long
    Dim n As Long
    Dim d As Double
    Application.ScreenUpdating = False
    m = Range("C" & Rows.Count).End(xlUp).Row
    For r = m To 3 Step -1
        a = Split(Range("C" & r).Value, ", ")
        n = UBound(a)
        If n > 0 Then
            d = Range("B" & r).Value / (n + 1)
            For i = n To 1 Step -1
                Range("A" & r).EntireRow.Copy
                Range("A" & r + 1).EntireRow.Insert
                Range("B" & r + 1).Value = d
                Range("C" & r + 1).Value = a(i)
            Next i
            Range("B" & r).Value = d
            Range("C" & r).Value = a(0)
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

[#3]

@Hans Vogelaar 

Thank you for your help that worked perfectly. I have never used a macro before and so i am a complete novice.

I have 19 other columns that are included in the spreadsheet. How do I include the other columns? similar to what you have done for column A,D & E

[#4]

@dw700d 

The macro that I posted copies and pastes entire rows, i.e. all columns.