Announcement

Collapse
No announcement yet.

Separate Comma Delimited Cells To New Rows

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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
        Triumph without peril brings no glory: Just try

        Comment


        • #5
          Re: Separate Comma Delimited Cells To New Rows

          Hi,

          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
          Kris

          ExcelFox

          Comment


          • #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
            Last edited by pearl.j; April 2nd, 2008, 03:23. Reason: Auto Merged Doublepost

            Comment


            • #7
              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
              Triumph without peril brings no glory: Just try

              Comment


              • #8
                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, 10:36. Reason: Auto Merged Doublepost

                Comment


                • #9
                  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

                  Comment


                  • #10
                    Re: Separate Comma Delimited Cells To New Rows

                    Hi John,

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

                    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
                    Kris

                    ExcelFox

                    Comment


                    • #11
                      Re: Separate Comma Delimited Cells To New Rows

                      PCI,

                      Success! Thank you for your macro.

                      I had overlooked the cell reference in this statement:
                      ROW = Range("AD" & Rows.Count).End(xlUp).Row


                      It was originally "C" but on the target worksheet the Item Number column is "AD".

                      I am not sure why the macro would successfully parse the test data? That has me puzzled.

                      The only remaining chore is to find out how to maintain the hyperlinked values assigned to one of the columns of data. Using the macro, only the original term is copied, but not its associated hyperlink. If you have any suggestions for this issue I would be grateful.

                      Thank you very much for the help, you have saved me many, many hours of tedious work.

                      Kind regards,

                      John

                      Comment


                      • #12
                        Re: Separate Comma Delimited Cells To New Rows

                        John,
                        Instead to copy only the value of the cell we need to copy the complete cell.
                        Code:
                         Cells(j, "F") = Cells(i, "B")
                        To be changed to
                        Code:
                           Cells(i, "B").Copy                          
                           ActiveSheet.Paste Destination:=Cells(j, "F")
                        Thanks Krish for your explanations, there is allways something to learn in your code.
                        Patrick
                        Attached Files
                        Triumph without peril brings no glory: Just try

                        Comment


                        • #13
                          Re: Separate Comma Delimited Cells To New Rows

                          Patrick,

                          Thanks again, that worked well.

                          Kris,

                          Thank you also. Obviously you currently operating a few orders of magnitude above my level but I appreciate your willingness to share. I will look into arrays and continue to try to fully understand your example when I have some more time.

                          Kind regards,

                          John

                          Comment


                          • #14
                            Re: Separate Comma Delimited Cells To New Rows

                            Hi everyone,

                            I'm new in the Forum. I'm sorry maybe my question may sound a little bit stupid but I have tried to duplicate the formula in order to have the values of a single cell separated by semicolon, one per row. No luck.
                            I'm attaching the file and as you can see, the column D is the value repeated... I can't separate them in different rows.

                            Could anyone help me... please!!!

                            thanks and B. Regards.
                            Attached Files

                            Comment


                            • #15
                              Re: Separate Comma Delimited Cells To New Rows

                              Hello mireillita,

                              Welcome to Ozgrid.

                              Posting your question in threads started by others is a violation of the forum rules and is known as thread hijacking. Posting solutions is acceptable.
                              ALWAYS start a new thread for your question and if you think it is helpful to clarify your thread you may include a link back this thread (or any other).

                              Start a new thread and be sure to give it a title that uses ONLY search friendly key words that accurately describe your thread content or overall objective. Do not use non-essential words like "help", "urgent", "I need", or assume a solution in the title by referencing Excel functions.
                              AAE
                              ----------------------------------------------------

                              Forum Rules | Message to Cross Posters | How to use Tags

                              Comment

                              Working...
                              X