Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Separate Comma Delimited Cells To New Rows

  1. #1
    Join Date
    31st March 2008
    Posts
    6

    Separate Comma Delimited Cells To New Rows

    Hello OzGrid,

    First time post, long time viewer.

    I have a 5K rows of data, each including a cell (AD) of multiple number values, separated by comma. I would like to create a new row for each unique number in column AD such that there remains only one unique value for every AD cell.

    If possible, I would prefer an in-cell formula rather than a macro.

    Your help is very appreciated.

    Thanks,

    John

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd October 2003
    Location
    Alsace France
    Posts
    4,099

    Re: Text To Row With Comma Delimited Data

    Not sure, but perhaps:
    Selecting AD column then DATA > TEXT to COLUMNS > DELIMITED > COMMA
    To finish COPY > SPECIAL PASTE > TRANSPOSE
    Else a small example could help.
    Triumph without peril brings no glory: Just try

  3. #3
    Join Date
    31st March 2008
    Posts
    6

    Re: Text To Row With Comma Delimited Data

    Thank you for your reply.

    I have included an example with four columns of data. The second column represents my specific issue.

    Several numeric values separated by a comma exist within each cell of this column. Each number represents an item number that was sold together with the other items in a combined invoice. The following column represents the shipping cost for all items.

    What I need to do is parse the second column in such a way that I have a unique item number for every row, one beneath the other. I will then divide the shipping cost among the total items and thereby begin to build net cost per item.

    Hopefully I am thinking along the most efficient track. Comments are welcomed.

    Thanks,

    John
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd October 2003
    Location
    Alsace France
    Posts
    4,099

    Re: Separate Comma Delimited Cells To New Rows

    If you want to do manualy a possibility could be:
    Install the Item Number column at the end
    Make DATA> TEXT to COLUMN as previously mentioned
    Adjust COST
    Else use the macro in the file attached and adapt it to your need, or send information to adjust it.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Triumph without peril brings no glory: Just try

  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Separate Comma Delimited Cells To New Rows

    Hi,

    VB:
    Sub kTest() 
        Dim a, i As Long, j As Long, w(), x, c  As Long 
        a = Range("b2:d" & Range("b" & Rows.Count).End(xlUp).Row) 
        Redim w(1 To UBound(a, 1) * 5, 1 To 3) 
        For i = 1 To UBound(a, 1) 
            x = Split(a(i, 2), ",") 
            For c = 0 To UBound(x) 
                j = j + 1: w(j, 1) = a(i, 1): w(j, 2) = x(c) 
                w(j, 3) = a(i, 3) / (UBound(x) + 1) 
            Next 
        Next 
        With Range("e1") 
            .Resize(, 3).Value = [{"Status","Item Number","Net Cost"}] 
            .Offset(1).Resize(j, 3).Value = w 
        End With 
    End Sub 
    
    
    HTH

  6. #6
    Join Date
    31st March 2008
    Posts
    6

    Re: Separate Comma Delimited Cells To New Rows

    Kris,

    This worked very well. I appreciate your help, it will save me many hours of tedious work.

    Any comments you will share about the code will be appreciated, I have typically used in-cell formulas in the past, and am now trying to lookup and understand terms like "Redim", "UBound", and what this formula "j = j + 1: w(j, 1) = a(i, 1): w(j, 2) = x(c)" means.

    Thank you again. This is a big help.

    John
    Auto Merged Post Until 24 Hrs Passes;

    Kris,

    Thank you again for your vb code. It works well as it is, though I will need to modify it in order for it to fit into the target worksheet. I have spent the past few hours trying to understand what you have written but it seems it is going to take me a long while to catch up.

    Unfortunately, I now seem to be mentally fixed on the challenge of understanding it, and am having difficulty concentrating on my other work.

    I have looked up the various functions and statements on MSDN and am presently hacking away at your code trying to understand it.

    As a first step, if you would clarify the intention of each variable that would be helpful. Initially I thought the w variable was intended to provide the Net Cost calculation, but I am no longer sure.

    If you can offer anything to help speed up my learning curve I would be very grateful.

    Thanks.

    John
    Auto Merged Post Until 24 Hrs Passes;

    Kris,

    Perhaps it would be easier if I provide a few additional columns of data, then I could identify the differences in the code and more easily move it forward.

    The challenge is that while the Item Number column must be parsed in rows, the other columns (there are over 35 in the original worksheet) must be replicated for each new row.

    If you would be willing to modify your first suggestion to handle the four additional columns I would be very appreciative.

    Thanks,

    John
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by pearl.j; April 2nd, 2008 at 03:23. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    23rd October 2003
    Location
    Alsace France
    Posts
    4,099

    Re: Separate Comma Delimited Cells To New Rows

    Pearl,
    It's not to sell you the macro previously prepared but if you stuck here attached an update of the first I sent you.
    There is Macro1 for Sheet1 and Macro2 for sheet2.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Triumph without peril brings no glory: Just try

  8. #8
    Join Date
    31st March 2008
    Posts
    6

    Re: Separate Comma Delimited Cells To New Rows

    PCI,

    Thank you for your suggestion. I have downloaded your attachment and am beginning to work with it. I appreciate the comments you enclosed.

    I will reply after a while to let you know how I am doing.

    Thanks again.

    John
    Auto Merged Post Until 24 Hrs Passes;

    PCI,

    I have made quite a bit of progress but I am calling it a night and will try to complete in the morning. I have successfully run the macro against a test set of 30 columns and 100 rows of normalized dummy data.

    When trying to run the macro against a sub set of the real data I am having an issue where the macro only prints the column headers but no data. I am wondering if there is something in the data that is breaking the macro?

    Hopefully, I will be fresher in the morning and will have more success.

    Thanks again for your help.

    John
    Last edited by pearl.j; April 2nd, 2008 at 10:36. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    23rd October 2003
    Location
    Alsace France
    Posts
    4,099

    Re: Separate Comma Delimited Cells To New Rows

    Perhaps could you prepare a short example with real data to see how it's running.
    Triumph without peril brings no glory: Just try

  10. #10
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Separate Comma Delimited Cells To New Rows

    Hi John,

    I'm not good in explaining the 'things'. Anyway have a look.

    VB:
    Sub kTest() 
        Dim a, i As Long, j As Long, w(), x, c  As Long 
         'stores the values in an array
        a = Range("b2:d" & Range("b" & Rows.Count).End(xlUp).Row) 
         'dimension of output array
        Redim w(1 To UBound(a, 1) * 5, 1 To 3) 
        MsgBox UBound(a, 1) 'see what's all about ubound
        MsgBox UBound(a, 2) 
         'loop starts within the array
        For i = 1 To UBound(a, 1) 
             'splits the second column value of the array
            x = Split(a(i, 2), ",") 
            For c = 0 To UBound(x) 
                 'stores each values in the array
                j = j + 1: w(j, 1) = a(i, 1): w(j, 2) = x(c) 
                w(j, 3) = a(i, 3) / (UBound(x) + 1) 
            Next 
        Next 
         'output the array value into a range
        With Range("e1") 
            .Resize(, 3).Value = [{"Status","Item Number","Net Cost"}] 
            .Offset(1).Resize(j, 3).Value = w 
        End With 
    End Sub 
    
    
    Also see more about Arrays

    HTH

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Convert A Column Into Comma Delimited List
    By kmar in forum Excel General
    Replies: 5
    Last Post: March 15th, 2013, 02:51
  2. Replies: 4
    Last Post: May 30th, 2008, 07:28
  3. Separate Comma Separated Cell Into Many Cells
    By bigtonyicu in forum Excel General
    Replies: 2
    Last Post: March 14th, 2008, 23:36
  4. Save As Comma Delimited Text File
    By badger55 in forum Excel General
    Replies: 4
    Last Post: January 14th, 2008, 22:11
  5. Pass comma-delimited list into an Array
    By cscribner in forum Excel General
    Replies: 3
    Last Post: January 28th, 2006, 08:27

Bookmarks

Posting Permissions

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