Comma delimited list and rows into columns

  • Currently this is what I have:
    Part No. | Description | Place | Manufacturer Name | Manufacturer Part No.
    123-456 | hi | | California | 300-123
    123-456 | hi | | China | 300-125
    456-789 | hello | A3, E5| Arizona | 400-126
    123-789 | hey | 15, C4, E2| Mexico | A32-123
    123-789 | hey | 15, C4, E2 | Germany | E21-654
    123-789 | hey | 15, C4, E2 | Italy | F11-354

    and i need it to be formatted as:
    Part No. | Description | Place | Manufacturer Name | Manufacturer Part No.
    123-456 | hi | | California | 300-123 |China | 300-125
    123-456 | hi | | California | 300-123 |China | 300-125
    456-789 | hello | A3| Arizona | 400-126
    456-789 | hello | E5| Arizona | 400-126
    123-789 | hey | 15| Mexico | A32-123 | Germany| E21-654 | Italy| F11-354
    123-789 | hey | C4| Mexico | A32-123 | Germany| E21-654 | Italy| F11-354
    123-789 | hey | E2| Mexico | A32-123 | Germany| E21-654 | Italy| F11-354

    There's a lot of things going on at once, but basically i need to separate the comma list into new rows and then for items with multiple manufacturers, list them next to each other rather than separating it as a new row.

    Thanks!

  • Re: Comma delimited list and rows into columns


    jstar88,

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

    To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.


    Have a great day,
    Stan

  • Re: Comma delimited list and rows into columns


    Try and comment.
    There is 2 steps and therefore 2 macros.

    Files

    • Oz2.zip

      (12.25 kB, downloaded 17 times, last: )

    Triumph without peril brings no glory: Just try

  • Re: Comma delimited list and rows into columns


    I came across another issue which is:
    I have a cell that says for instance R260-R263.

    Can't think of a macro of how to separate it in new lines so that it becomes:
    R260
    R261
    R262
    R263

    Thanks! similar to commas but now with hyphen. is the code similar as well?

  • Re: Comma delimited list and rows into columns


    New revision for PREPARE1

  • Re: Comma delimited list and rows into columns


    Thanks PCI but when I run it, I get a Subscript out of range error. Does this code include possible blanks in Column C?

    Currently, this is the code I am using for the separation of the commas:



    After separating the commas, there are still hyphens. Now I need to run some macro that identifies the hyphens and fills in the consecutive numbers in between those hyphens on separate rows?
    For example,
    [INDENT]i need to try and get from "R260-R263" (with the hyphen and only the numbers 260 and 263) in one cell to become:

    R260
    R261
    R262
    R263
    [/INDENT]

  • Re: Comma delimited list and rows into columns


    See here the file used.
    Here again send a sample of your data to be sure we are working on the data.

    Files

    • Oz3.zip

      (14.36 kB, downloaded 16 times, last: )

    Triumph without peril brings no glory: Just try

  • Re: Comma delimited list and rows into columns


    I attached a little change to the file and i get an error of type mismatch:

    Do you mind explaning to me what is the reason for the error after you take a look?
    I think it has to do something with: first separating the commas and then after that, going through column C and finding the hyphens.
    along with recording the R at the beginning, sometimes it could be two or more letters in the front as well such as RA

    Thanks for all the help! I really appreciate it!

  • Re: Comma delimited list and rows into columns


    The issue we are running into is how to extract the place number:
    Is there 1 or 2 characters at the beginning?
    Is there only 3 digits at the end?
    There could be what ever we want just tell it

  • Re: Comma delimited list and rows into columns


    Quote from PCI;558421

    Of course the last digits make a number greater on after the hyphen versus the number before the hyphen.



    Can you help me adapt your macro to accomodate varying combinations of the "C" column?

    For instance, I can have:
    DS1-DS10 in a cell
    A1234
    1,2,A3,C3-C5

  • Re: Comma delimited list and rows into columns


    As the format of the place is becoming more sophisticated (mixe of comma and hyphen) it will take more time (for me) to prepare.

  • Re: Comma delimited list and rows into columns


    Perhaps it is not so long to prepare.
    Here 3 macros to be launched sequentially:
    PREPARE1a, Prepare1b, PREPARE2
    Of courese you can merge them after checking

    Files

    • Oz5.zip

      (12.76 kB, downloaded 21 times, last: )

    Triumph without peril brings no glory: Just try

  • Re: Comma delimited list and rows into columns


    As soon as you load the files with the macros, the macros are available for all files on the computer.
    The macros prepared are working for the active sheet of the active file.
    So just copy your file (withour data) in an other PC.

  • Re: Comma delimited list and rows into columns


    hi PCI!
    sry to bother you after so long but after running the awesome macro, i came across a problem:

    For the place column within your attached Oz file: I get an error when I have something like U12_4-U12_7 as the place. Do you know I can adjust my macro so that it works out to:
    U12_4
    U12_5
    U12_6
    U12_7

    thanks!