How to split the Last column values into multipe rows

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Danny ChildIT ManagerCommented:
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.
vivekrautelaAuthor 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.

Professor JMicrosoft Excel ExpertCommented:
vba solution or formula?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

vivekrautelaAuthor Commented:
Any script like Shell or Perl.
Saurabh Singh TeotiaCommented:
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??

vivekrautelaAuthor 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.
Saurabh Singh TeotiaCommented:
Use this 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
    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
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Professor JMicrosoft Excel ExpertCommented:
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
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
            x = Split(.Value)
            .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 dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.