Create new line for each slash in field

[simonstaton]

Hi,

I have a complicated problem that I have spent hours on with no use so have decided to turn to experts for help. I need a vba script to convert the following:

- sku - category - price -
- 103 - dog/test/test2 - 50 -
- 104 - dog/test/test5 - 60 -

into:

-sku - category - price -
- 103 - [blank] - 50 -
- [blank] - dog - [blank] -
- [blank] - dog/test - [blank] -
- [blank] - dog/test/test2 - [blank] -
-104 - [blank] - 60 -
- [blank] - dog - [blank] -
- [blank] - dog/test - [blank] -
- [blank] - dog/test/test5 - [blank] -

So seperating the "category column" into individual lines but keeping the root category for each section there and making a new line for each. So making one line of "dog/test/test2" into 4 lines of "[blank] - dog - dog/test - dog/test/test2" confusing I know! And also making all data on the new category lines blank but keeping the original lines data just making the original category field blank.

Again been going mad over this, the lines are around 23000 so doing this manually is no a go, you guys are my only hope now.

An image of my actual data is here:
http[space]://i45[dot]tinypic[dot]com/4gjbes.jpg

A bit more info: there are more columns but did 3 to make it simple, the category is on column "E" and again there are 23000 lines. The sheet is called "testdata" I also have a script here that does sort of what I need to but it is only making new lines and not doing the above:

[VBA]
Sub test()
Dim a, i As Long, ii As Long, e, n As Long
Dim b(), txt As String, x As Long
With Range("a1").CurrentRegion
a = .Value
txt = Join$(Application.Transpose(.Columns(3).Value))
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "/"
x = .Execute(txt).Count * 2
End With
Redim b(1 To UBound(a, 1) + x, 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
If a(i, 3) <> "" Then
For Each e In Split(a(i, 3), "/")
n = n + 1
For ii = 1 To UBound(a, 2)
b(n, ii) = a(i, ii)
Next
b(n, 3) = Trim$(e)
Next
End If
Next
.Resize(n).Value = b
End With
End Sub
[/VBA]

Thankyou for any advice!
Simon

[magelan]

I would greatly simplify your code.

use a for loop to go through b2 and split by /. if a split exists, insert a new row and format your output appropriately.

EDIT:

Please go through and name your varaibles something aside from LETTERS!!!

Letters are only acceptable for FOR LOOP COUNTERS! even then if you have more than I J P R you should go through and rename your for counters as actual words so you know what youre counting.

[simonstaton]

I would greatly simplify your code.

use a for loop to go through b2 and split by /. if a split exists, insert a new row and format your output appropriately.

EDIT:

Please go through and name your varaibles something aside from LETTERS!!!

Letters are only acceptable for FOR LOOP COUNTERS! even then if you have more than I J P R you should go through and rename your for counters as actual words so you know what youre counting.

Thankyou so much for the reply, on the verge of thinking this is lost cause . I I don't have any experience with vba so those words are a bit over my head would it be possible for you to reedit my code? In the dark a bit on this one as I am just a web developer and have only used vba once before

[magelan]

Thankyou so much for the reply, on the verge of thinking this is lost cause . I I don't have any experience with vba so those words are a bit over my head would it be possible for you to reedit my code? In the dark a bit on this one and have got quite a lot on the line for me to find a fix :O

edit: I got the code from a post a found, it does something along the lines of what I want but did not write it

I'm working on a code for this right now that you would be able to just change the values in and have it do what you want [since it intrigued me and i will probably need something like this soon]

basically...all those variables named "i" and "ii" and "p" and "r" are what I like to refer to as indian code... all of the coders I know that learned in india dont make their code viewer friendly because it was never taught to them. To make it more readable to yourself and others you should rename those variables and find-replace them to make the code understandable [depending on if you know what they are]

since you didnt write the code i'm guessing you dont know what each variable does so dont worry about it, but let me try and get this code block done for you.

[simonstaton]

I'm working on a code for this right now that you would be able to just change the values in and have it do what you want [since it intrigued me and i will probably need something like this soon]

basically...all those variables named "i" and "ii" and "p" and "r" are what I like to refer to as indian code... all of the coders I know that learned in india dont make their code viewer friendly because it was never taught to them. To make it more readable to yourself and others you should rename those variables and find-replace them to make the code understandable [depending on if you know what they are]

since you didnt write the code i'm guessing you dont know what each variable does so dont worry about it, but let me try and get this code block done for you.

Fantastic, thankyou and yes I can understand I am a web developer so always try and name my variables in stuff like javascript so they are easily followed and referenced. might have to start learning vba looks very interesting

Also to make things even more complicated for you just realised one of the categorys is "puppy / adult" with a space at either side of the "/" to say it is not a category but actually the name of one, is it possible to not count the "/" if it has a space at either side? Thankyou again

Also if you want to see what the actual data looks like you can see it here: http[space]://i45[dot]tinypic[dot]com/4gjbes.jpg

[magelan]

Fantastic, thankyou and yes I can understand I am a web developer so always try and name my variables in stuff like javascript so they are easily followed and referenced. might have to stop learning vba looks very interesting

i am actually very new to VBA myself [started in september] but have been coding some major full on programs for a company now.. learning a lot and definitely learning its limitations. VBA can be fun though - just to see what you can do

[simonstaton]

i am actually very new to VBA myself [started in september] but have been coding some major full on programs for a company now.. learning a lot and definitely learning its limitations. VBA can be fun though - just to see what you can do

I see, well this stupidly complicated script should be a test of your skills

Also here is what it should be doing for each line: http:[SPACE]//i50[DOT]tinypic.com/4rc5m1[DOT]jpg

[magelan]

[vba]
Option Explicit
Sub splitEmUp()
Dim splitter() As String 'this is storage space for the split function
Dim i As Integer ' main-loop for counter "which cell we are on"
Dim j As Integer ' splitter for-loop counter "which section of the split are we on"
Range("b2").Activate 'starting in cell b2 because row 1 is headers and category is located in the B column

For i = 0 To 50 'from beginning to end i=0 means b2, i=1 means b3
splitter = Split(ActiveCell.Offset(i, 0), "/") 'split the cell based on / and store it in splitter
If (UBound(splitter)) > 0 Then 'if a split occurred
ActiveCell.Offset(i, 0).Value = "" 'set the activecell to blank
ActiveCell.Offset(i + 1, 0).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'insert a new row and shift everything down

ActiveCell.Offset(i + 1, 0).Value = splitter(0) 'initialize the "Down" cells

For j = 1 To UBound(splitter)
ActiveCell.Offset(i + j + 1).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'create another row if it needs to
ActiveCell.Offset(i + (j + 1), 0).Value = ActiveCell.Offset(i + j).Value & "/" & splitter(j) 'fill out the new row
Next
i = i + UBound(splitter) + 1 'need to step I past the new cells
ReDim splitter(0)
Erase splitter 'erase and eliminate splitter to avoid carry over.

End If
Next

End Sub

[/vba]
of course like i said you will need to change the variable ranges in this [or make functions that dynamically get the variable range - HOHO!!!] but as it stands this will find everything in Column B that is a / and step it out.

[simonstaton]

Option Explicit
Sub splitEmUp()
    Dim cells As Range
        Set cells = Range("A2:C500") 'the range you are trying to hit skipping headers
    Dim cellVar As Range
        Set cellVar = Range("B2") 'this will be the currently modified cell skipping headers to start at first real cell
    Dim splitter() As String 'this is storage space for the split function
    Dim i As Integer ' main-loop for counter "which cell we are on"
    Dim j As Integer ' splitter for-loop counter "which section of the split are we on"
    Range("b2").Activate 'starting in cell b2 because row 1 is headers and category is located in the B column

   For i = 0 To 50 'from beginning to end i=0 means b2, i=1 means b3
        splitter = Split(ActiveCell.Offset(i, 0), "/") 'split the cell based on / and store it in splitter
        If (UBound(splitter)) > 0 Then 'if a split occurred
            ActiveCell.Offset(i, 0).Value = "" 'set the activecell to blank
            Debug.Print i
            ActiveCell.Offset(i + 1, 0).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'insert a new row and shift everything down
            
            ActiveCell.Offset(i + 1, 0).Value = splitter(0) 'initialize the "Down" cells
            
            For j = 1 To UBound(splitter)
                ActiveCell.Offset(i + j + 1).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'create another row if it needs to
                ActiveCell.Offset(i + (j + 1), 0).Value = ActiveCell.Offset(i + j).Value & "/" & splitter(j) 'fill out the new row
            Next
            i = i + UBound(splitter) + 1 'need to step I past the new cells
            ReDim splitter(0)
            Erase splitter 'erase and eliminate splitter to avoid carry over.
            
        End If
    Next

End Sub

Fantastic, worked a dream. Seemed to stop on the 50th line though, any way to increase it to 24000?

Edit: just figured it out, changed the to 50. Hands down you are doing very good for one month learning! Thanks again really appreciated

[magelan]

Fantastic, worked a dream. Seemed to stop on the 50th line though, any way to increase it to 24000?

I've edited my code above and taken out some unnecessary things.

As a student to VBA you should be able to look at my code and see where it needs to be changed in order to give you full range to 24000 - I labeled everything pretty well for you!

Hint - its the big loop that drives everything

[simonstaton]

I've edited my code above and taken out some unnecessary things.

As a student to VBA you should be able to look at my code and see where it needs to be changed in order to give you full range to 24000 - I labeled everything pretty well for you!

Hint - its the big loop that drives everything

Yes have managed to figure it out, the notes are very helpful just going over how it works now. Now for the complicated bit! :P is there anyway to have it ignore the "/" if it has a space at either side so " / " = not a new line but "/" = new line

Just brainstorming now how I could do this, would an if statement work? I imagine you have these in vba? So if charecter = " / " ignore; if charecter = "/" do this

[magelan]

Yes have managed to figure it out, the notes are very helpful just going over how it works now. Now for the complicated bit! :P is there anyway to have it ignore the "/" if it has a space at either side so " / " = not a new line but "/" = new line

BTW - If you didnt, copy my new code from above - I changed it right after I posted it because I noticed some parts I thought i was going to use but didnt.

In order to add that functionality, I think the best thing to do would be to add a filter in - basically an if statement.

Enclose the entire For loop in an if-statement that utilizes the instring functions. It would look like

[vba]
If (instr(activecell.offset(i,0).value," / ") = 0 then
for.....
....
...
end if
[/vba]

Note- however - that this code will ignore lines that look like "var/text / spaced" as well as lines that look like " var / spaced"

If you need stuff that has "var/text / spaced" then i will have to build something a little more complicated [character substitution!]

[simonstaton]

BTW - If you didnt, copy my new code from above - I changed it right after I posted it because I noticed some parts I thought i was going to use but didnt.

In order to add that functionality, I think the best thing to do would be to add a filter in - basically an if statement.

Enclose the entire For loop in an if-statement that utilizes the instring functions. It would look like

If (instr(activecell.offset(i,0).value," / ") = 0 then 
 for.....
 ....
 ...
end if

Note- however - that this code will ignore lines that look like "var/text / spaced" as well as lines that look like " var / spaced"

If you need stuff that has "var/text / spaced" then i will have to build something a little more complicated [character substitution!]

Okay have done and will give this a go, so it will jump the entire field if it has " / " in not just ignore that part of the field?

[magelan]

Okay have done and will give this a go, so it will jump the entire field if it has " / " in not just ignore that part of the field?

It will jump the entire field, yes. We can build something in if you want it to still separate /'s while keeping " / "'s in the same field.

[simonstaton]

It will jump the entire field, yes. We can build something in if you want it to still separate /'s while keeping " / "'s in the same field.

If we could that would be good, so making: /test/test2/test / tests into:

/test
/test/test2
/test/test2/test / tests

[magelan]

If we could that would be good, so making: /test/test2/test / tests into:

/test
/test/test2
/test/test2/test / tests

We need to use character substitution then - i will replace all " / " with "!@#" and then replace them back when we are done. Check it out!

[vba]
Option Explicit
Sub splitEmUp()
Dim splitter() As String 'this is storage space for the split function
Dim i As Integer ' main-loop for counter "which cell we are on"
Dim j As Integer ' splitter for-loop counter "which section of the split are we on"
Range("b2").Activate 'starting in cell b2 because row 1 is headers and category is located in the B column

For i = 0 To 50 'from beginning to end i=0 means b2, i=1 means b3
ActiveCell.Offset(i, 0).Value = Replace(ActiveCell.Offset(i, 0).Value, " / ", "!@#")
splitter = Split(ActiveCell.Offset(i, 0), "/") 'split the cell based on / and store it in splitter
If (UBound(splitter)) > 0 Then 'if a split occurred
ActiveCell.Offset(i, 0).Value = "" 'set the activecell to blank
Debug.Print i
ActiveCell.Offset(i + 1, 0).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'insert a new row and shift everything down

ActiveCell.Offset(i + 1, 0).Value = splitter(0) 'initialize the "Down" cells
ActiveCell.Offset(i + 1, 0).Value = Replace(ActiveCell.Offset(i + 1, 0).Value, "!@#", " / ")
For j = 1 To UBound(splitter)
ActiveCell.Offset(i + j + 1).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'create another row if it needs to
ActiveCell.Offset(i + (j + 1), 0).Value = ActiveCell.Offset(i + j).Value & "/" & splitter(j) 'fill out the new row
ActiveCell.Offset(i + (j + 1), 0).Value = Replace(ActiveCell.Offset(i + (j + 1), 0).Value, "!@#", " / ")
Next
i = i + UBound(splitter) + 1 'need to step I past the new cells
ReDim splitter(0)
Erase splitter 'erase and eliminate splitter to avoid carry over.

End If
Next

End Sub
[/vba]

Time for me to head home though!

[simonstaton]

We need to use character substitution then - i will replace all " / " with "!@#" and then replace them back when we are done. Check it out!

Option Explicit
Sub splitEmUp()
    Dim splitter() As String 'this is storage space for the split function
    Dim i As Integer ' main-loop for counter "which cell we are on"
    Dim j As Integer ' splitter for-loop counter "which section of the split are we on"
    Range("b2").Activate 'starting in cell b2 because row 1 is headers and category is located in the B column

   For i = 0 To 50 'from beginning to end i=0 means b2, i=1 means b3
        ActiveCell.Offset(i, 0).Value = Replace(ActiveCell.Offset(i, 0).Value, " / ", "!@#")
        splitter = Split(ActiveCell.Offset(i, 0), "/") 'split the cell based on / and store it in splitter
        If (UBound(splitter)) > 0 Then 'if a split occurred
            ActiveCell.Offset(i, 0).Value = "" 'set the activecell to blank
            Debug.Print i
            ActiveCell.Offset(i + 1, 0).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'insert a new row and shift everything down
            
            ActiveCell.Offset(i + 1, 0).Value = splitter(0) 'initialize the "Down" cells
            ActiveCell.Offset(i + 1, 0).Value = Replace(ActiveCell.Offset(i + 1, 0).Value, "!@#", " / ")
            For j = 1 To UBound(splitter)
                ActiveCell.Offset(i + j + 1).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'create another row if it needs to
                ActiveCell.Offset(i + (j + 1), 0).Value = ActiveCell.Offset(i + j).Value & "/" & splitter(j) 'fill out the new row
                ActiveCell.Offset(i + (j + 1), 0).Value = Replace(ActiveCell.Offset(i + (j + 1), 0).Value, "!@#", " / ")
            Next
            i = i + UBound(splitter) + 1 'need to step I past the new cells
            ReDim splitter(0)
            Erase splitter 'erase and eliminate splitter to avoid carry over.
            
        End If
    Next

End Sub

Oh awesome, very clever so we mass replace all mentions of it then run the document then write them back in. I like it, well this seems to do the job! Thankyou so much for the help dude probably saved me a few days of running in circles.