-2

I'm pulling data from our database to send out to a 3rd party according to their template. I need to send guardian information, with each person having their own row. Our database has the guardian information with a "/" and then details by each ID #. I need to split the data according to the Guardian cell while duplicating everything else.

enter image description here

Can this be done in Excel without any special add ons? All help is appreciated.

AnemonePoppy
  • 51
  • 1
  • 11
  • 2
    https://stackoverflow.com/questions/42425208/vba-split-cell-values-into-multiple-rows-and-keep-other-data – Scott Craner Dec 15 '17 at 17:08
  • https://stackoverflow.com/questions/35439099/how-to-split-a-single-cell-into-multiple-rows-and-add-another-row – Scott Craner Dec 15 '17 at 17:11
  • https://stackoverflow.com/questions/25776914/vba-to-split-multi-line-text-in-a-excel-cell-into-separate-rows-and-keeping-adja – Scott Craner Dec 15 '17 at 17:12
  • Is this a one-time requirement? You need to split the existing rows once, or do you need to automatically split them when future data data is entered? Either way, no add-ons required. :-) – ashleedawg Dec 15 '17 at 17:42

2 Answers2

1

Scott Craner linked to the correct answer using a VBA (which was new to me) but was exactly what I needed.

Sub splitByColB()
    Dim r As Range, i As Long, ar
    Set r = Worksheets("Sheet1").Range("B999999").End(xlUp)
    Do While r.row > 1
        ar = Split(r.value, "/")
        If UBound(ar) >= 0 Then r.value = ar(0)
        For i = UBound(ar) To 1 Step -1
            r.EntireRow.Copy
            r.Offset(1).EntireRow.Insert
            r.Offset(1).value = ar(i)
        Next
        Set r = r.Offset(-1)
    Loop
End Sub
AnemonePoppy
  • 51
  • 1
  • 11
1

Image shown here. Cell N1 is the delimiter. In this case a slash.

Helper:=SUM(K1,LEN(B1)-LEN(SUBSTITUTE(B1,$N$1,"")))+1

You must fill the above formula one row more. Then change K2 to 1 manually because there is a slash in B1.

A11:=a1

Fill this formula to the right.

A12:=LOOKUP(ROW(1:1),$K:$K,A:A)&""

Fill this formula to the right and down.

B12:=MID($N$1&LOOKUP(ROW(A1),$K:$K,B:B)&$N$1,FIND("艹",SUBSTITUTE($N$1&LOOKUP(ROW(A1),$K:$K,B:B)&$N$1,$N$1,"艹",ROW(A2)-LOOKUP(ROW(A1),$K:$K)))+1,FIND("艹",SUBSTITUTE($N$1&LOOKUP(ROW(A1),$K:$K,B:B)&$N$1,$N$1,"艹",ROW(A2)-LOOKUP(ROW(A1),$K:$K)+1))-FIND("艹",SUBSTITUTE($N$1&LOOKUP(ROW(A1),$K:$K,B:B)&$N$1,$N$1,"艹",ROW(A2)-LOOKUP(ROW(A1),$K:$K)))-1)&""

Fill down.

Bug:

Numbers will be converted to Text. Of course you can remove the &"" at the end of the formula, but blank cells will be filled with 0.

user3471272
  • 55
  • 6