Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 35

Thread: Redistribute a Delimited Column Of Data into Separate Rows (Keeping Other Data As Is)

  1. #21
    Junior Member
    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    Rick,

    I'm glad for, somehow, have contributed to a better solution.

    New code copied for future use.

    Regards,

    M.

  2. #22
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    very nice. i was looking for this code. coincidentally, i searched before posting the question in Mrexcel forum and here i got exactly what i needed.

    thank you Rick and Marcelo.
    Last edited by Excel Fox; 08-18-2013 at 08:39 PM. Reason: Quote removed as it didn't add value to the post

  3. #23
    Junior Member
    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0
    Nice piece of code, although I see a problem, when a value is blank in another collumn, it is copied from the cell that contains a value, so it filles other empty cells with bogus data

    Eg.

    AA BBB 1:2
    CC 2:3

    Will become:

    AA BBB 1
    AA BBB 2
    CC BBB 2
    CC BBB 3

    Any solution for this ?

  4. #24
    Junior Member
    Join Date
    Nov 2013
    Posts
    1
    Rep Power
    0

    Multiple multi-value columns

    Hello. This macro works beautifully, however is there a way to do this same function to multiple columns with multi-values?

    My data looks like this:

    Column 1 Column 2 Multi-value Column Multi-value column
    Data Data RMA;-;RMA 65044;99944;65033

    The multi-values all correspond with each other.

    This is a break down of service work orders, line items such as labor, travel, parts and if it is returnable.

    Your help is greatly appreciated.

  5. #25
    Junior Member
    Join Date
    Jul 2014
    Posts
    1
    Rep Power
    0

    Almost exactly what I am looking for - but issue with blanks in end columns

    Hi Rick,
    This is great and is almost exactly what I am looking for. I have two (hopefully small) issues with the code though.

    What I changed: I updated the range from A:C to be A:Z,and to start at Row 6 (headings in row 5) as per my data. Then have created the macro twice to once sperate column G and then next Column E. This works great (although I am sure you could do this in one - but it is beyond me to manipulate it that much).

    Issue 1: comes when I look at the copied data results. Column U to Y have a heading in Row 5 but no data/are blank (they are for use later on). So no data should have been copied. But instead the code copies the headings from Row 5...?? ANy ideas on how to fix this?

    Issue 2: comes when I have 2 columns column C+D that I need to split together - C is material number and D is material Name.
    e.g
    material number Material Name
    100017, 100018 Mat A, Mat B

    Should become
    Mateiral Number Material Name
    100017 Mat A
    100018 Mat B

    If I was to run the eixisting macro twice for Column C then D I would actually get after the first run
    100017 Mat A, Mat B
    100018 Mat A, Mat B
    Then after the second run
    100017 Mat A
    100017 Mat B
    100018 Mat A
    100018 Mat B

    Any ideas?

    Many Thanks in advance!
    Last edited by Excel Fox; 07-30-2014 at 03:40 PM. Reason: Removed Quote

  6. #26
    Junior Member
    Join Date
    Sep 2014
    Posts
    3
    Rep Power
    0

    Empty cell

    Quote Originally Posted by Rick Rothstein View Post
    *** NOTE - Revised code posted August 12, 2013 in response ***
    *** to a problem Marcelo Branco pointed out in Message #19 ***

    The following scenario seems to come up somewhat often at the various forums I participate in... take a table of data where one column contains delimited data and split that delimited data so that each item is on a row of its own, copying the associated data into the blank cells created by the split. Visually, we want to go from this table...

    A B C D
    1 Name Client
    Number
    Parts
    Ordered
    2 Rick 1111111 P1, P2, P3
    3 Sam 2222222 P2, P5
    4 Joe 3333333 P3
    5 Bill 4444444 P4, P6, P7, P8
    6

    to this one
    A B C D
    1 Name Client
    Number
    Parts
    Ordered
    2 Rick 1111111 P1
    3 Rick 1111111 P2
    4 Rick 1111111 P3
    5 Sam 2222222 P2
    6 Sam 2222222 P5
    7 Joe 3333333 P3
    8 Bill 4444444 P4
    9 Bill 4444444 P6
    10 Bill 4444444 P7
    11 Bill 4444444 P8
    12

    Below is a macro that will accomplish this task. Note though that I have generalize it somewhat. Usually in the requests the delimited data is in the last column as shown above, however, there is no need for this to be the case... this macro will allow any column to be the delimited column.

    Code:
    Sub RedistributeData()
      Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String
      Const Delimiter As String = ", "
      Const DelimitedColumn As String = "C"
      Const TableColumns As String = "A:C"
      Const StartRow As Long = 2
      Application.ScreenUpdating = False
      LastRow = Columns(TableColumns).Find(What:="*", SearchOrder:=xlRows, _
                SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
      For X = LastRow To StartRow Step -1
        Data = Split(Cells(X, DelimitedColumn), Delimiter)
        If UBound(Data) > 0 Then
          Intersect(Rows(X + 1), Columns(TableColumns)).Resize(UBound(Data)).Insert xlShiftDown
        End If
        If Len(Cells(X, DelimitedColumn)) Then
          Cells(X, DelimitedColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
        End If
      Next
      LastRow = Cells(Rows.Count, DelimitedColumn).End(xlUp).Row
      On Error Resume Next
      Set Table = Intersect(Columns(TableColumns), Rows(StartRow).Resize(LastRow - StartRow + 1))
      If Err.Number = 0 Then
        Table.SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
        Columns(DelimitedColumn).SpecialCells(xlFormulas).Clear
        Table.Value = Table.Value
      End If
      On Error GoTo 0
      Application.ScreenUpdating = True
    End Sub
    There are four constants (the Const statements) that you need to match to your actual worksheet conditions in order to work. The first is named Delimiter and it can be one or more characters long. The second is named DelimitedColumn and specifies the column letter containing the delimited cells. The third is named TableColumns and it specifies the columns occupied by your data (which must always include the column with the delimited cells. The last one is named StartRow and it specifies the row containing the first piece of data (that is, it is the row number below the headers, if any).



    Hi, There occurs a problem with the other columns. Some of the columns are not fully completed, I mean half of the column has something in it and the other half is empty. When I run the code, it fullfills the other rest of the column with the value of last unempty cell. It stretches the last cell to the length of the longest column. Does any body have an idea with this code to fix it?

    Thanks in advance,
    Regards,

  7. #27
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by aselcuks View Post
    When I run the code, it fullfills the other rest of the column with the value of last unempty cell. It stretches the last cell to the length of the longest column. Does any body have an idea with this code to fix it?
    That is how the code was designed. When data is missing and the user wants to fill it in, normally they want to do that fill for the data item and, since there is no way to know how far that last item should be filled for, I chose to use the row with the last data item as the stopping point. Until your post, that is the functionality everyone has wanted. If you post a sample worksheet with existing data on Sheet1 and what you want that data to look like on Sheet2, I will try to modify the code for you to do what you show me you want.

  8. #28
    Junior Member
    Join Date
    Sep 2014
    Posts
    3
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    That is how the code was designed. When data is missing and the user wants to fill it in, normally they want to do that fill for the data item and, since there is no way to know how far that last item should be filled for, I chose to use the row with the last data item as the stopping point. Until your post, that is the functionality everyone has wanted. If you post a sample worksheet with existing data on Sheet1 and what you want that data to look like on Sheet2, I will try to modify the code for you to do what you show me you want.

    MMVAL V1_BOM.xls

    That is the sheet I wanna modify and after the code executed I want it to be like;



    MMVAL V1_BOM_modified.xls


    but It fullfills the rest of column K with the last statement "C15" and it goes down to length of longest column.


    Thank you for your concern.

    Regards

  9. #29
    Junior Member
    Join Date
    Sep 2014
    Posts
    3
    Rep Power
    0
    Quote Originally Posted by aselcuks View Post
    MMVAL V1_BOM.xls

    That is the sheet I wanna modify and after the code executed I want it to be like;



    MMVAL V1_BOM_modified.xls


    but It fullfills the rest of column K with the last statement "C15" and it goes down to length of longest column.


    Thank you for your concern.

    Regards



    Also if there is any blank cell lets say G20, after execution it fulfills the shifted cell of G20 with the value of G19, it doesnt leave it blank as it was before.

  10. #30
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Code:
    Sub M_snb()
        sn = Sayfa1.Cells(1).CurrentRegion
        sp = Split(Join(Application.Transpose(Application.Index(sn, 0, 4)), ","), ",")
    
        c00 = "1_"
        For j = 2 To UBound(sn)
           c00 = c00 & Replace(Space(sn(j, 3)), " ", j & "_")
        Next
        st = Application.Index(sn, Application.Transpose(Split(c00, "_")), [transpose(row(1:12))])
        
        For j = 2 To UBound(st) - 1
           st(j, 4) = sp(j - 1)
        Next
        
        Sayfa1.Cells(40, 1).Resize(UBound(st) - 1, UBound(st, 2)) = st
    End Sub

Similar Threads

  1. Move data from rows into columns for every unique value
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-13-2013, 03:02 AM
  2. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  3. Replies: 3
    Last Post: 03-16-2013, 05:13 PM
  4. Replies: 12
    Last Post: 08-19-2012, 06:17 PM
  5. Transpose data into Rows
    By vikash200418 in forum Excel Help
    Replies: 2
    Last Post: 04-10-2012, 11:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •