New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Split cell and add the row data for each item

Macros, VBA, Excel Automation, etc.

Split cell and add the row data for each item

Postby lalitkanth86 » Tue Feb 09, 2016 7:11 am

Hi All,

I usually take dump form multiple resources and merge the data to get the required output.

But in the recent past I am getting multiple items in a single cell and the respective row data is same for all.

EX:
Item Code---------Product---Cost -------Area

F1------------------Car--------7,45,000--Region1
F2, F3 -------------Bike------45,000-----Region2
F4------------------Cycle------15,000----Region3

Desired result:
Item Code--------Product----Cost -------Area

F1-----------------Car---------7,45,000--Region1
F2-----------------Bike--------45,000----Region2
F3 ----------------Bike--------45,000----Region2
F4-----------------Cycle-------15,000----Region3

Can some one suggest me how to split F2 & F3 and copy the respective record next to the row.

I have over 5000 records and over 10 items in a single cell.

Thanks,
Lalit Kanth Vutpala
  • 0

lalitkanth86
Rookie
 
Posts: 1
Joined: Feb 9, 2016
Reputation: 0
Excel Version: 10

Re: Split cell and add the row data for each item

Postby NoSparks » Tue Feb 09, 2016 3:54 pm

This assumes your data starts in A2 with row 1 being headers.

On a copy of your worksheet, try this

Code: Select all
Sub Lalit()
    Dim ws As Worksheet         'the worksheet to use
    Dim lr As Long              'last row
    Dim lc As Long              'last column
    Dim j As Long               'row we'll be working with
    Dim s As String             'col A cell contents
    Dim arr                     'array of cell contents
    Dim Rng2copy                'array of cells to copy
    Dim i As Integer
   
Application.ScreenUpdating = False

Set ws = Sheets("Sheet1")    ' <~~~~ change as required

With ws
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    'work from the bottom up
    For j = lr To 2 Step -1
        With .Cells(j, 1)
            s = .Value
            If InStr(1, s, ",") Then
                lc = ws.Cells(j, Columns.Count).End(xlToLeft).Column
                Rng2copy = ws.Range(ws.Cells(j, 2), ws.Cells(j, lc))
                arr = Split(s, ",")
                .Offset(1, 0).Resize(UBound(arr), 1).EntireRow.Insert
                For i = 0 To UBound(arr)
                    .Offset(i, 0).Value = Trim(arr(i))
                    .Offset(i, 1).Resize(1, lc - 1).Value = Rng2copy
                Next i
            End If
        End With
    Next j
End With

Application.ScreenUpdating = True

End Sub
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 60 guests

cron