Sep 29 2020 08:10 AM
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 |
Sep 29 2020 08:48 AM
SolutionRun 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
Sep 29 2020 01:07 PM
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
Sep 29 2020 01:30 PM
The macro that I posted copies and pastes entire rows, i.e. all columns.
Sep 29 2020 08:48 AM
SolutionRun 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