dealing with macros/mid-find and text to column

[Deinoc]

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!:

RAW
Tablet ID	Placement	s_det	notes	language	date_added	last_update_date
1	0	Elk (3)		English	11/15/2017 18:52	11/15/2017 18:52
2	0	Elk (1)|Rosemont (4)|Main (15)	English	11/15/2017 18:55	11/15/2017 19:01
3	0	Elk (23)|Fairgrounds (1)	English	11/15/2017 18:52	11/15/2017 18:33
4	0	Elk (1)|Rosemont (4)|Main (15)|Fairgrounds (3)	English	11/15/2017 18:55	11/16/2017 20:41
Desired OUTPUT 1
Tablet ID	Placement	s_det	Amount	notes	language	date_added	last_update_date
1	0	Elk	3		English	11/15/2017 18:52	11/15/2017 18:52
2	0	Elk	1		English	11/15/2017 18:55	11/15/2017 19:01
2	0	Rosemont	4		English	11/15/2017 18:55	11/15/2017 19:01
2	0	Main	15		English	11/15/2017 18:55	11/15/2017 19:01
3	0	Elk	23		English	11/15/2017 18:52	11/15/2017 18:33
3	0	Fairgrounds	1		English	11/15/2017 18:52	11/15/2017 18:33
4	0	Elk	1		English	11/15/2017 18:55	11/16/2017 20:41
4	0	Rosemont	4		English	11/15/2017 18:55	11/16/2017 20:41
4	0	Main	15		English	11/15/2017 18:55	11/16/2017 20:41
4	0	Fairgrounds	3		English	11/15/2017 18:55	11/16/2017 20:41
desired OUTPUT 2
		Elk	Rosemont	Main	Fairgrounds
1	0	3				English	11/15/2017 18:52	#########
2	0	1	4	15		English	11/15/2017 18:55	#########
3	0	23	1			English	11/15/2017 18:52	#########
4	0	1	4	15	3	English	11/15/2017 18:55	#########

<tbody>
</tbody>

 

[Fixed]

Can you share the CSV file? - I want to try some methods for import.

 

[Deinoc]

Can you share the CSV file? - I want to try some methods for import.

Here is a csv: https://www.dropbox.com/sh/tk8q7e23tt09f1p/AADwObDT2Uc6o6MLQx_5E2dUa?dl=0

series is what I need to make changes. Thanks

 

[Fixed]

I think only macro can do what you want.

 

[Deinoc]

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?

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?

 

[Fluff]

Hi this should do the first part of your request

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

 

[Fixed]

I guess there is one thing you missed in your laconic macro – spaces in the end of cell values in "K" column.

 

[Fluff]

Good point, but easily remedied, if that's going to be a problem for the OP.

 

[Fixed]

My macro is big enough and doesn't look fast, but works correctly I hope:

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