Databases
StackOverflow2013
Postsdbo
POExcel/VBA Breakdown field
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel/VBA Breakdown field
    primarykey
    data
    text
    <p>Bit of a complicated task I have to do but I will try and explain. I have an excel file with 23000 lines of data which I am importing into a website. Each one has a field like so:</p> <pre><code>Category | other data | other data 2 Foods/Dog/Treats Pre-Pack | 1223 | image.jpg </code></pre> <p>I need it to grab each line and add a new line below it for each "/" so turning the above into:</p> <pre><code>Category | other data | other data 2 [blank in original line] | 1223 | image.jpg Foods | [blank field] | [blank field] Foods/Dog | [blank field] | [blank field] Foods/Dog/Treats Pre-Pack | [blank field] | [blank field] </code></pre> <p>So the script needs to add a new line for each category but keeping the original category in front of it. So turning <code>category/category2/category 3</code> into 4 lines of: <code>[blank] - category - category/category2 - category/category2/category 3</code></p> <p>Does anyone know a way or script to do this?</p> <p>Thanks, Simon</p> <p>Note: The worksheet is called "test" and the category column starts at E2 and goes to E23521</p> <p>I have the following script:</p> <pre><code>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(5).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, 5) &lt;&gt; "" Then For Each e In Split(a(i, 5), "/") n = n + 1 For ii = 1 To UBound(a, 2) b(n, ii) = a(i, ii) Next b(n, 5) = Trim$(e) Next End If Next .Resize(n).Value = b End With End Sub </code></pre> <p>This seems to create a new row as I need it to but does not keep the slash structuring moving up with each one. And also dosnt add a blank line on all the new ones and make the original category value blank.</p> <p>SOLVED:</p> <pre><code>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("E2").Activate 'starting in cell e2 because row 1 is headers and category is located in the B column For i = 0 To 24000 'from beginning to end i=0 means e2, i=1 means e3 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)) &gt; 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 &amp; "/" &amp; 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 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBrad
    1. USSimon Staton
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTDuplicate
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel/VBA Breakdown field
      1. USPowerUser
      1. VTFavorite
    2. VO
      singulars
      1. POExcel/VBA Breakdown field
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POExcel/VBA Breakdown field
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COIs this a one-time thing or do you need code to do this automatically? If it's a one-time task, I'd suggest playing around with Excel's built in functions.
      singulars
      1. POExcel/VBA Breakdown field
      1. USPowerUser
    2. COThankyou for the reply, this is a one time thing to be run once. I am terrible at excel and have no idea what I am doing but do know you can make macros. Any advice is needed really I have 2 days to get this done and on the verge of going insane :)
      singulars
      1. POExcel/VBA Breakdown field
      1. USSimon Staton
    3. COWell done :) the format of the site is generally to make an answer and mark it as the `Accepted` solution instead of writing `Solved` in the question which is more typical of traditional forums.
      singulars
      1. POExcel/VBA Breakdown field
      1. USBrad
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload