SOLVED

Split column add row

%3CLINGO-SUB%20id%3D%22lingo-sub-1724871%22%20slang%3D%22en-US%22%3ESplit%20column%20add%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1724871%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%2026%2C000%20rows.%3C%2FP%3E%3CP%3EWhen%20there%20are%20multiple%20codes%20in%20the%20code%20column%20I%20would%20like%20to%20create%20a%20new%20row%20and%20split%20the%20spend%20amounts%20evenly%20between%20each%20code%20in%20the%20column.%3C%2FP%3E%3CP%3EFor%20example%2C%20case%20%23%206%20has%203%20codes.%20I%20would%20like%20to%20create%20a%20new%20row%20for%20each%20code%20and%20split%20the%20dollar%20amount%20evenly%20across%20all%20the%203%20codes.%20Thanks%20in%20advance%20for%20your%20help%3C%2FP%3E%3CP%3E%3CSTRONG%3EOriginal%3C%2FSTRONG%3E%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%3ECase%26nbsp%3B%20%23%3C%2FTD%3E%3CTD%20width%3D%22128%22%3ESpend%3C%2FTD%3E%3CTD%20width%3D%22238%22%3ECode%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EMaster%20Project(s)%3C%2FTD%3E%3CTD%20width%3D%2278%22%3EDate%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287px%22%20height%3D%2230px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22128px%22%20height%3D%2230px%22%3E%24900%3C%2FTD%3E%3CTD%20width%3D%22238px%22%20height%3D%2230px%22%3ESB25%2C%20RT3SD%2C%20JI890%3C%2FTD%3E%3CTD%20width%3D%22150px%22%20height%3D%2230px%22%3EF%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2230px%22%3E1%2F6%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAfter%20Splitting%20Codes%3C%2FSTRONG%3E%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%3ECase%26nbsp%3B%20%23%3C%2FTD%3E%3CTD%20width%3D%22128%22%3ESpend%3C%2FTD%3E%3CTD%20width%3D%22238%22%3ECode%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EMaster%20Project(s)%3C%2FTD%3E%3CTD%20width%3D%2278%22%3EDate%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22128px%22%3E%24300%3C%2FTD%3E%3CTD%20width%3D%22238px%22%3ESB25%3C%2FTD%3E%3CTD%20width%3D%22150px%22%3EF%3C%2FTD%3E%3CTD%20width%3D%2275px%22%3E1%2F6%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22128px%22%3E%24300%3C%2FTD%3E%3CTD%20width%3D%22238px%22%3ERT3SD%3C%2FTD%3E%3CTD%20width%3D%22150px%22%3EF%3C%2FTD%3E%3CTD%20width%3D%2275px%22%3E1%2F6%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22128px%22%3E%24300%3C%2FTD%3E%3CTD%20width%3D%22238px%22%3EJI890%3C%2FTD%3E%3CTD%20width%3D%22150px%22%3EF%3C%2FTD%3E%3CTD%20width%3D%2275px%22%3E1%2F6%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EData%20Set%3C%2FSTRONG%3E%3C%2FP%3E%3CTABLE%20width%3D%22681%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%3ECase%26nbsp%3B%20%23%3C%2FTD%3E%3CTD%20width%3D%22128%22%3ESpend%3C%2FTD%3E%3CTD%20width%3D%22238%22%3ECode%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EMaster%20Project(s)%3C%2FTD%3E%3CTD%20width%3D%2278%22%3EDate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E(%24853)%3C%2FTD%3E%3CTD%20width%3D%22238%22%3ET3640%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EA%3C%2FTD%3E%3CTD%3E1%2F1%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%2426%2C185%3C%2FTD%3E%3CTD%20width%3D%22238%22%3EAK30%2C%20A484%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EC%3C%2FTD%3E%3CTD%3E1%2F3%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E4%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%24184%2C383%3C%2FTD%3E%3CTD%20width%3D%22238%22%3ETBLP%2C%20UH07%3C%2FTD%3E%3CTD%20width%3D%22150%22%3ED%3C%2FTD%3E%3CTD%3E1%2F4%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E5%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%242%2C192%3C%2FTD%3E%3CTD%20width%3D%22238%22%3EAK045%2C%20D24G%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EE%3C%2FTD%3E%3CTD%3E1%2F5%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%24900%3C%2FTD%3E%3CTD%20width%3D%22238%22%3ESB25%2C%20RT3SD%2C%20JI890%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EF%3C%2FTD%3E%3CTD%3E1%2F6%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E20%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%24546%3C%2FTD%3E%3CTD%20width%3D%22238%22%3EM643%2C%20SS00%3C%2FTD%3E%3CTD%20width%3D%22150%22%3ET%3C%2FTD%3E%3CTD%3E1%2F20%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E26%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%24114%2C432%3C%2FTD%3E%3CTD%20width%3D%22238%22%3EUI7%2C%20UFR%2C%20UE95%2C%20UE52%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EZ%3C%2FTD%3E%3CTD%3E1%2F26%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E27%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%2420%2C962%3C%2FTD%3E%3CTD%20width%3D%22238%22%3EUG34%2C%20UGFR%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EAA%3C%2FTD%3E%3CTD%3E1%2F27%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E28%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%2464%2C689%3C%2FTD%3E%3CTD%20width%3D%22238%22%3EUG77%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EAB%3C%2FTD%3E%3CTD%3E1%2F28%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E29%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%24229%2C271%3C%2FTD%3E%3CTD%20width%3D%22238%22%3ETBLP%2C%20U80%2C%20U76%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EAC%3C%2FTD%3E%3CTD%3E1%2F29%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E32%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%24125%2C366%3C%2FTD%3E%3CTD%20width%3D%22238%22%3ETLP%2C%20UG1%2C%200FR%2C%20U76%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EAF%3C%2FTD%3E%3CTD%3E2%2F1%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E47%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%2413%2C538%3C%2FTD%3E%3CTD%20width%3D%22238%22%3EAK36%2C%20AK04%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EAU%3C%2FTD%3E%3CTD%3E2%2F16%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E48%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%243%2C279%3C%2FTD%3E%3CTD%20width%3D%22238%22%3EXP17%2C%20XR14%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EAV%3C%2FTD%3E%3CTD%3E2%2F17%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E49%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%248%2C970%3C%2FTD%3E%3CTD%20width%3D%22238%22%3EAK84%3C%2FTD%3E%3CTD%20width%3D%22150%22%3EAW%3C%2FTD%3E%3CTD%3E2%2F18%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1724871%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1725132%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20column%20add%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1725132%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813299%22%20target%3D%22_blank%22%3E%40dw700d%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERun%20the%20following%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20SplitData()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20a()%20As%20String%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20Dim%20d%20As%20Double%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Range(%22C%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20For%20r%20%3D%20m%20To%203%20Step%20-1%0A%20%20%20%20%20%20%20%20a%20%3D%20Split(Range(%22C%22%20%26amp%3B%20r).Value%2C%20%22%2C%20%22)%0A%20%20%20%20%20%20%20%20n%20%3D%20UBound(a)%0A%20%20%20%20%20%20%20%20If%20n%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20d%20%3D%20Range(%22B%22%20%26amp%3B%20r).Value%20%2F%20(n%20%2B%201)%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20i%20%3D%20n%20To%201%20Step%20-1%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20r).EntireRow.Copy%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20r%20%2B%201).EntireRow.Insert%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22B%22%20%26amp%3B%20r%20%2B%201).Value%20%3D%20d%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22C%22%20%26amp%3B%20r%20%2B%201).Value%20%3D%20a(i)%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20i%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22B%22%20%26amp%3B%20r).Value%20%3D%20d%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22C%22%20%26amp%3B%20r).Value%20%3D%20a(0)%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20r%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1726314%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20column%20add%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1726314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%20that%20worked%20perfectly.%20I%20have%20never%20used%20a%20macro%20before%20and%20so%20i%20am%20a%20complete%20novice.%3C%2FP%3E%3CP%3EI%20have%2019%20other%20columns%20that%20are%20included%20in%20the%20spreadsheet.%20How%20do%20I%20include%20the%20other%20columns%3F%20similar%20to%20what%20you%20have%20done%20for%20column%20A%2CD%20%26amp%3B%20E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1726361%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20column%20add%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1726361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813299%22%20target%3D%22_blank%22%3E%40dw700d%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20macro%20that%20I%20posted%20copies%20and%20pastes%20entire%20rows%2C%20i.e.%20all%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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  #SpendCodeMaster Project(s)Date
6$900SB25, RT3SD, JI890F1/6/2020

 

After Splitting Codes

Case  #SpendCodeMaster Project(s)Date
6$300SB25F1/6/2020
6$300RT3SDF1/6/2020
6$300JI890F1/6/2020

 

 

Data Set

Case  #SpendCodeMaster Project(s)Date
1($853)T3640A1/1/2020
3$26,185AK30, A484C1/3/2020
4$184,383TBLP, UH07D1/4/2020
5$2,192AK045, D24GE1/5/2020
6$900SB25, RT3SD, JI890F1/6/2020
20$546M643, SS00T1/20/2020
26$114,432UI7, UFR, UE95, UE52Z1/26/2020
27$20,962UG34, UGFRAA1/27/2020
28$64,689UG77AB1/28/2020
29$229,271TBLP, U80, U76AC1/29/2020
32$125,366TLP, UG1, 0FR, U76AF2/1/2020
47$13,538AK36, AK04AU2/16/2020
48$3,279XP17, XR14AV2/17/2020
49$8,970AK84AW2/18/2020

 

 

3 Replies
Highlighted
Best Response confirmed by dw700d (New Contributor)
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
Highlighted

@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

Highlighted

@dw700d 

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