Splitting pipe delimited cell into named columns

[phil6011]

Hi all

Hoping someone may be able to help something I'm stumped on.

We have a hierarchy file where the core data comes in a pipe delimited cell like below.

network_CAR|subnetwork_GARAGE|subnetwork_DEALER|KMBINDIA|KMBINDIA_1|KMBINDIA_2|KMBINDIA_3

I need something that takes this and outputs like the below


[TABLE="width: 500"]
<tbody>[TR]
[TD]network[/TD]
[TD]subnetwork[/TD]
[TD]location[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD]GARAGE|DEALER[/TD]
[TD]KMBINDIA|KMBINDIA_1|KMBINDIA_2|KMBINDIA_3[/TD]
[/TR]
</tbody>[/TABLE]


Everything other than 'location' codes is proceeded by columnname_XXX i.e. 'network_xxx' means Network is the name of the column and everything after the underscore is the value

Where the 'columnname' is repeated more than once it should only create one column for that name and then pipe-delimit all values referenced as in the subnetwork 'GARAGE|DEALER' example

Each row isn't necessarily consistent in what values it has either, some may skip the Subnetwork_XXX and just have Network_xxx & location values for example

Feels like I'm asking a potentially impossible task but wanted to check for sure.

Any help much appreciated!

Thanks

 

[BiocideJ]

I believe you can do the following:
Open the txt file in Excel. (This should put everyting in column A as a long pipe-delimited text field.
Next, under the Data heading, choose Text to Columns.
In the resulting wizard, choose the option for Delimited and click Next
In the next screen put a pipe character in the field next to Other (and make sure other is checked)
You can look into the last page of the wizard, but likely at this point you can just click FINISH.

..voila. You should have the output you are looking for.


EDIT: I need to read the whole request before i post.
Let me think on this a little more. Are you opposed to a VBA solution because I suspect that is what will be required.

 

[BiocideJ]

OK.. Here is a VBA solution that worked with the limited testing I was able to perform including multiple network, no networks, multiple subnetworks, no subnetworks.

Let me know if this doesn't do what you are needing.

Sub ExpandPipeNetwork()
Dim srcSht As Worksheet
Dim dstSht As Worksheet
Dim firstsrcROW As Long, srcCOL As String
Dim dstROW As Long
Dim arr As Variant
Dim i As Long, arrI As Long
Dim strNetwork As String, strSubNetwork As String, strLocation As String


'***EDIT values in this section***


Set srcSht = Sheets("Sheet1")   'adjust this to the name of the sheet where the pipe delimited text exists


firstsrcROW = 2                 'adjust this based on the row where the first value exists (e.g. 1 if no headers)


srcCOL = "A"                    'the column where the pipe-delimited text exists


Set dstSht = Sheets("Sheet2")   'adjust this to the name of the sheet where you want to store the results
                                'PLEASE NOTE ALL EXISTING DATA ON THE DESTINATION SHEET WILL BE ERASED
                                
'****SHOULDN'T NEED TO EDIT BELOW THIS POINT***






dstSht.Cells.Clear
dstSht.Range("A1:C1").Value = Array("network", "subnetwork", "location")
dstROW = 2


For i = firstsrcROW To srcSht.Range("A" & srcSht.Rows.Count).End(xlUp).Row    'iterate through all src rows


    arr = Split(srcSht.Cells(i, srcCOL), "|")
    strNetwork = ""
    strSubNetwork = ""
    strLocation = ""
    
    For arrI = LBound(arr) To UBound(arr)
        
        If InStr(arr(arrI), "subnetwork_") > 0 Then
            strSubNetwork = strSubNetwork & IIf(strSubNetwork = "", "", "|") & Replace(arr(arrI), "subnetwork_", "")
        ElseIf InStr(arr(arrI), "network_") > 0 Then
            strNetwork = strNetwork & IIf(strNetwork = "", "", "|") & Replace(arr(arrI), "network_", "")
        Else
            strLocation = strLocation & IIf(strLocation = "", "", "|") & arr(arrI)
        End If
    Next arrI
    
    dstSht.Cells(dstROW, "A").Value = strNetwork
    dstSht.Cells(dstROW, "B").Value = strSubNetwork
    dstSht.Cells(dstROW, "C").Value = strLocation
    dstROW = dstROW + 1
Next i


dstSht.UsedRange.EntireColumn.AutoFit   'AutoFit column widths
MsgBox "DONE"


End Sub

 

[phil6011]

thanks so much for this, going to try today as been offline all weekend. will report back!