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 chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
126
Peter_SSs
Peter_SSs
V
  • Solved
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
114
djclements
djclements
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
598
Alex Blakenburg
A
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
50
Sufiyan97
Sufiyan97
A
  • Solved
Calculate monthly booked percentage of hotel rooms
2 3
Replies
21
Views
342
arendberg
A

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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