How to split the Last column values into multipe rows

vivekrautela
vivekrautela used Ask the Experts™
on
Hi All,

I've data that i've to import in CSV format. What i want to do is I want to split the values that are in column N under "Other Permissions" to multiple rows. so the values from Column A to Column M will remain same and the values under column N will come one by one in each row. I've attached the two CSV files for your ref.

Existing Ent Report.csv  --> The original CSV
Required Ent Report.csv --> The required format CSV.

The Data highlighted in yellow will remain same for all rows and the one highlighted in blue will get split up and come as one value for each row.

Please help how i can split the data using the script.

regards
Vivek
Existing-Ent-Report.csv
Required-Ent-Report.csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Danny ChildIT Manager

Commented:
You can use Text to Columns using the Delimited option with ; as your delimiter to split it into multiple columns.
After that, you'll need Copy.. Paste Special.. Transpose to turn those specific columns into rows.
If you want this scripted, you'll also have to count the number of rows to insert above the one below.

Author

Commented:
I want to automate the process because the import will run 3 times a week and i don't want to do a manual process for that. The sample i added has only one row that i splited manually. however in the production data i've around 200+ rows so manual process is not possible.

Regards
Vivek
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
vba solution or formula?
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
Any script like Shell or Perl.
Top Expert 2015

Commented:
Vivek a quick question..Basis of my observation row-2 and row-3 in your data file is duplicate..correct me if im wrong??

The only unique value is the IP address which is coulmn-G..Now you want to do this only for the rows where their is some value in Column-G??

Saurabh...

Author

Commented:
Hi Saurabh,

I've attached new files for your ref.

if you check the users i've 2 users Master & Administrator.

For user Master Column A to Column M Information is change. However the values in Column N is splitted manually into separate rows and updated in the new file "Required Ent Report New"

Similarly user "Administrator" has same information from column A to column M only the inforamation in N is separated in multiple column.

Hope this will help.
Existing-Ent-Report_New.csv
Required-Ent-Report_New.csv
Top Expert 2015
Commented:
Use this code..it will automatically create worksheet with the name of summary which has the data the way you are looking for..

Sub populatedata()

    Dim cell As Range, rng As Range
    Dim lrow As Long, ws As Worksheet, ws1 As Worksheet
    Dim str As Variant, str1 As Variant, lr As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set ws1 = Sheets("Existing-Ent-Report_New")
    lrow = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set rng = ws1.Range("N2:N" & lrow)
    On Error Resume Next
    Sheets("Summary").Delete
    On Error GoTo 0
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Summary"
    Set ws = ActiveSheet
    ws1.Range("A1").EntireRow.Copy ws.Range("A1")
    For Each cell In rng
        str = Split(cell.Value, ";")
        For Each str1 In str
            lr = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1

            cell.EntireRow.Copy ws.Range("a" & lr)
            ws.Range("N" & lr).Value = str1
        Next str1
    Next cell
    ws.Cells.EntireColumn.AutoFit
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Open in new window


Saurabh...
Professor JMicrosoft Excel Expert
Top Expert 2014
Commented:
this code will prompt you to select the column that has data with delimiter and once you select that column then it will transform the data as you described

Sub SplitMacro()
Dim LR As Long, i As Long, LC As Integer
Dim x As Variant
Dim r As Range, iCol As Integer
On Error Resume Next
Set r = Application.InputBox("Click in the column to split by", Type:=8)
On Error GoTo 0
If r Is Nothing Then Exit Sub
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = " "
On Error GoTo 0
iCol = r.Column
Application.ScreenUpdating = False
LC = Cells(1, Columns.Count).End(xlToLeft).Column
LR = Cells(Rows.Count, iCol).End(xlUp).Row
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = " "
On Error GoTo 0
Columns(iCol).Insert
With Range(Cells(1, iCol + 1), Cells(LR, iCol + 1))
    .Replace What:=",", Replacement:=" ", LookAt:=xlPart
    .Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart
    .Replace What:=Chr(13), Replacement:=" ", LookAt:=xlPart
    .Replace What:=";", Replacement:=" ", LookAt:=xlPart
End With
For i = LR To 1 Step -1
    With Cells(i, iCol + 1)
        If InStr(.Value, " ") = 0 Then
            .Offset(, -1).Value = .Value
        Else
            x = Split(.Value)
            .Offset(1).Resize(UBound(x)).EntireRow.Insert
            .Offset(, -1).Resize(UBound(x) - LBound(x) + 1).Value = Application.Transpose(x)
        End If
    End With
Next i
Columns(iCol + 1).Delete
LR = Cells(Rows.Count, iCol).End(xlUp).Row
With Range(Cells(1, 1), Cells(LR, LC))
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error GoTo 0
    .Value = .Value
End With
With ActiveSheet.UsedRange
    .Replace What:=" ", Replacement:=vbNullString, LookAt:=xlWhole
End With
Application.ScreenUpdating = True
End Sub

Open in new window

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial