Split data separated with comma into multiple rows

  • Hi,


    I have to process a data set which has the following format:


    Code
    Waiting for ##### team   ABxxxxxxxxxxx       ABCDE4FG25HI7J5K, LMN123OPQ45RST, UV12398765WXYZ
    Waiting for $$$$ team       AByyyyyyyyyyy    95597836355278,9666382677292
    Waiting for @@@@ team   ABzzzzzzzzzzz       MBAWAXZSE1234TU


    My target is to bring it in the following format:


    Code
    Waiting for ##### team   ABxxxxxxxxxxx    ABCDE4FG25HI7J5K
    Waiting for ##### team   ABxxxxxxxxxxx    LMN123OPQ45RST
    Waiting for ##### team   ABxxxxxxxxxxx    UV12398765WXYZ
    Waiting for $$$$ team       AByyyyyyyyyyy  95597836355278
    Waiting for $$$$ team       AByyyyyyyyyyy  9666382677292
    Waiting for @@@@ team   ABzzzzzzzzzzz     MBAWAXZSE1234TU


    With values separated by comma to be entered in the row below it.


    Can someone please suggest me a vba code to accomplish the above task?


    Edit:


    Detailed explanation of problem:


    1. I have a very huge data set with 10,000+ rows and 28 or more columns.
    2. My data has 2 IDs, one is identification number and 2nd is multiple sub-identifications under it separated by commas.
    3. Each sub-identification under the identification number has one issue associated with it.
    4. So my task was to bifurcate sub-identification numbers separated by comma into multiple lines and use a combo of Identification number and Sub-identification number as a Unique ID to map the resolution status of all issues associated with an identification number.


    The same question has been asked at:


    http://www.mrexcel.com/forum/excel-questions/737163-split-data-separated-comma-into-multiple-rows.html

  • Re: Split data separated with comma into multiple rows


    I am guessing it is a 3 column table.. assuming that... heres one way..


    It will dump the result in columns D:F


  • Re: Split data separated with comma into multiple rows


    Got one answer which suited my need perfectly. Reposting it here for anyone else who might need it.
    This has been answered by BrianMH from Mr Excel forum:


  • Re: Split data separated with comma into multiple rows


    Quote from apo;690378

    It was also answered by me (who you chose to ignore).. both my question and solution..


    I think you need to read the Rules regarding Cross Posting..


    I didn't mean. Sorry If I made any violations.


    1. I have a very huge data set with 10,000+ rows and 28 or more columns, dumping the data from columns 1 to 3 into further cells was not serving my purpose.
    2. My data has 2 IDs, one is identification number and 2nd is multiple sub-identifications under it separated by commas.
    3. Each sub-identification under the identification number has one issue associated with it.
    4. So my task was to bifurcate sub-identification numbers separated by comma into multiple lines and use a combo of Identification number and Sub-identification number as a Unique ID to map the resolution status of all issues associated with an identification number.


    Edit: I found out that I need to mention if I have posted the same question in some other forum. Will follow it in my future posts.

  • Re: Split data separated with comma into multiple rows


    k..right.. I think you will find my solution did the same thing except it dumped the result in the columns next to the original columns.
    Your initial post(minus the recent edit) lacked any info that would make that a less desirable option than any other..


    Just add the link to the cross post in this thread and you will be 'inside' the rules once more..

  • Re: Split data separated with comma into multiple rows


    Done.


    Added link in the OP.


    And thanks for taking time to answer my question. I will follow all the forum rules in future.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!