Match a Word From A List Comma Separated in a Cell

  • Hello,


    I would like to list my product by using of a dropdown. Then, when a product selected in a cell via dropdown, its fee rate percentage must have appeared. However, I don't know how to overcome this. Because products have been categorized and each category is in a cell as seperated by a comma. I worked on a partial match solution but it didn't work. Any help would be great. For some reasons, I do not want to break those categories, though helper can be okay if applicable. Or any VBA solution at the last analysis. Thanks. File is in the attachment.

    Files

    • test1.xlsx

      (8.89 kB, downloaded 21 times, last: )

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Carim

    Changed the title of the thread from “Match a Word From A List Comma Seperated in a Cell” to “Match a Word From A List Comma Separated in a Cell”.
  • Wow, That is incredible Carim I cannot thank you enough. All seems perfect. Just one more question... Normally, there are too many categories more than this sample workbook; is there a practical method to pull out each product? I tried this method: Text to columns then paste as transpose but it will take ages.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Glad to hear this could help you out :)


    Thanks for your Thanks ...AND for the Like :thumbup:


    Regarding your last question ... usually TextToColumns does a pretty good job ...

    You can also use a a macro with Split ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Should have added that ... in case of difficulties ... you can always attach your very long list to the Forum ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    In order to create your Product List from many rows where cells contain your Comma-Separated products ...


    Have re-arranged your test file and added a macro to Create your List ... which you can, then, use as a Data Validation ...


    With this worksheet configuration, you can expand as much as needed the Product List in Column A ;)


    Attached is your V3 file


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you sooo much Carim, Wonderful! You made my day again. I'm crying of happiness now :thumbup:

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]