dealing with macros/mid-find and text to column

D

Deinoc

New Member
Joined
Nov 23, 2017
Messages
6
So I have this problem when importing a csv file, one cell has information that needs to be split into columns or repeated with the same tablet id. I have tried several combinations but everything takes time and it is not clean and it doesnt solve the problem, any ideas data is below and the two outputs are what I am trying to split, thanks!:



[TABLE="class: cms_table, width: 868"]
<tbody>[TR]
[TD]RAW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tablet ID[/TD]
[TD]Placement[/TD]
[TD]s_det[/TD]
[TD]notes[/TD]
[TD]language[/TD]
[TD]date_added[/TD]
[TD]last_update_date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]Elk (3)[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Elk (1)|Rosemont (4)|Main (15)[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Elk (23)|Fairgrounds (1)[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Elk (1)|Rosemont (4)|Main (15)|Fairgrounds (3)[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desired OUTPUT 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tablet ID[/TD]
[TD]Placement[/TD]
[TD]s_det[/TD]
[TD]Amount[/TD]
[TD]notes[/TD]
[TD]language[/TD]
[TD]date_added[/TD]
[TD]last_update_date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Rosemont[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Main[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]Fairgrounds[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Rosemont[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Main[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Fairgrounds[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]desired OUTPUT 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Elk[/TD]
[TD]Rosemont[/TD]
[TD]Main[/TD]
[TD]Fairgrounds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: center"]#########[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: center"]#########[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: center"]#########[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: center"]#########[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Fixed said:
1. I think only macro can do what you want.
2. If I understand correctly in this sample CSV the column named as "series_details" is column "s_det" from your first post?
Click to expand...

Yes that is correct. I have done macros, and I can deal with a couple rows but I cant automatize the process. Adding new columns and then adding the right amount to each column is not working out. I tried to delimit by pipe "|" and created new columns at the end of each row, but then I cant match all of the columns as there are situations that the first column is not the same to just change Elk(3) to just 3, if we have fairgrounds as the same column different row...

Is there a horizontal sort? meaning that if I select an area that needs to be sorted as column AC ELK only, AD fairgrounds , AE Rosemont and so on with a bunch of spaces in between?
 
Upvote 0
Hi this should do the first part of your request
Code: 
Sub SplitAddRows()

    Dim Qty As Long
    Dim Cnt As Long
    
    For Cnt = Range("K" & Rows.Count).End(xlUp).Row To 2 Step -1
        Qty = UBound(Split(Range("K" & Cnt), "|"))
        If Qty > 0 Then
            Rows(Cnt + 1).Resize(Qty).Insert
            Rows(Cnt).Resize(Qty + 1).FillDown
            Range("K" & Cnt).Resize(Qty + 1).Value = Application.Transpose(Split(Range("K" & Cnt), "|"))
        End If
    Next Cnt
    Columns(12).Insert
    Columns(11).TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
    Other:=True, OtherChar:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1))
    Range("L1").Value = "Amount"
    Columns(12).Replace ")", "", xlPart, xlByRows, , False, False

End Sub
 
Upvote 0
I guess there is one thing you missed in your laconic macro – spaces in the end of cell values in "K" column.
 
Upvote 0
Good point, but easily remedied, if that's going to be a problem for the OP.
 
Upvote 0
My macro is big enough and doesn't look fast, but works correctly I hope:

Code: 
Sub ModExportF()
Dim ExpColumn As Variant
Dim Amount As Variant
Dim SDet As Variant
Dim x As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim CN As Integer
Dim XX As Integer
Dim YY As Integer
Dim tmpstr As String
Dim b As Boolean
    
    XX = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    YY = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    ExpColumn = ActiveSheet.Cells(1, 1).Resize(YY, XX).Value
    
    For i = 1 To XX
    If ExpColumn(1, i) = "series_details" Then
    If InStr(ExpColumn(2, i), "(") > 0 Then
    CN = i
    b = True
    Exit For
    Else
    b = False
    End If
    End If
    Next i
    
    If b = True Then
    
    Application.ScreenUpdating = False
    
    For i = 1 To CN
    ActiveSheet.Cells(1, i).Value = ExpColumn(1, i)
    Next i
    ActiveSheet.Cells(1, CN + 1).Value = "series_details_amount"
    ActiveSheet.Columns(CN + 1).AutoFit
   
    For i = CN + 2 To XX + 1
    ActiveSheet.Cells(1, i).Value = ExpColumn(1, i - 1)
    Next i
    Range("A1", Range("A1").End(xlToRight)).HorizontalAlignment = xlCenter
    
    k = 1
    
    For j = 2 To YY
    tmpstr = ExpColumn(j, CN)
On Error Resume Next
    With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = True
    .Pattern = "\d+"
     x = .Execute(tmpstr).Count - 1
     ReDim Amount(0 To x)
     
     For i = 0 To x
     Amount(i) = .Execute(tmpstr).Item(i)
     Next i
     
     .Pattern = "\(([^)]+)\)"
     ReDim SDet(0 To x)
     tmpstr = Replace((.Replace(tmpstr, "")), "|", "")
     SDet = Split(Mid(tmpstr, 1, Len(tmpstr) - 1))
     End With
     
    For i = 1 To x + 1
    Rows(k + i).Insert
    ActiveSheet.Cells(k + i, CN).Value = SDet(i - 1)
    ActiveSheet.Cells(k + i, CN + 1).Value = Amount(i - 1)
    
    For l = 1 To CN - 1
    ActiveSheet.Cells(k + i, l).Value = ExpColumn(j, l)
    Next l
    
    For l = CN + 2 To XX + 1
    ActiveSheet.Cells(k + i, l).Value = ExpColumn(j, l - 1)
    Next l
    
    Next i
    
    k = k + x + 1
    
    Next j
    
    j = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    ActiveSheet.Rows(k + 1 & ":" & j).Delete
    
    End If
    
    Application.ScreenUpdating = True
    
On Error GoTo 0
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
83
Peter_SSs
Peter_SSs
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
566
Alex Blakenburg
A
tabbytomo
  • Question Question
=IF Exact formula stops working part way through
Replies
1
Views
248
arthurbr
arthurbr
D
  • Solved
Groupby include only counta < 4
Replies
5
Views
211
Alex Blakenburg
A
O
  • Solved
Macro to extract repeating invoice numbers and details
Replies
4
Views
235
Omar M
O

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top