+ Reply to Thread
Results 1 to 7 of 7

Find multiple words and replace the adjacent column with one word

  1. #1
    Registered User
    Join Date
    03-13-2011
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    17

    Find multiple words and replace the adjacent column with one word

    I need to find multiple words in one column and replace them with one word in the adjacent one.

    I have this headings

    Category SubCategory
    word1
    word2
    word3

    Each category is related to several subcategories, so I need to find multiple subcategories, and when it finds a match replace the blank space under Category with their corresponding category.

    Like This

    Category SubCategory
    Home Theater Movies & TV
    Home Theater Home Theater in a Box
    Home Theater another subcategory

    now I have only the subcategories, I need to fill the categories with the macro or script.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Find multiple words and replace the adjacent column with one word

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  3. #3
    Registered User
    Join Date
    03-13-2011
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Find multiple words and replace the adjacent column with one word

    Quote Originally Posted by davesexcel View Post
    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
    Thanks for tip I am attaching a dummy in XLS format.
    Please note I haven't included all the categories(as there are a few more) neither all the subcategories, but then it's justa a matter of adding them in the finished project.

    There is a Before and after example.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: Find multiple words and replace the adjacent column with one word

    If I understand the problem, vlookup is the solution. I have added a data table in a seperate tab with the appropriate lookup values, you will have to finish filling out the table for all posible combinations. Once you get the data table filled out you can copy the formula in A2 down as far as necessary. Also in the vlookup formula I assumed you are on excel 2003 so I used if(iserror) instead of Iferror() for error checking.

    Regards,

    Tom
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-13-2011
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Find multiple words and replace the adjacent column with one word

    Quote Originally Posted by tom.hogan View Post
    If I understand the problem, vlookup is the solution. I have added a data table in a seperate tab with the appropriate lookup values, you will have to finish filling out the table for all posible combinations. Once you get the data table filled out you can copy the formula in A2 down as far as necessary. Also in the vlookup formula I assumed you are on excel 2003 so I used if(iserror) instead of Iferror() for error checking.

    Regards,

    Tom
    hi tom, in fact I'm using Excel 2007 ... but posted in XLS, because I saw others used that format ...iferror is command for Excel 2007?

  6. #6
    Registered User
    Join Date
    03-13-2011
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Find multiple words and replace the adjacent column with one word

    Quote Originally Posted by ic3cold View Post
    hi tom, in fact I'm using Excel 2007 ... but posted in XLS, because I saw others used that format ...iferror is a command for Excel 2007?
    Thanks I wil try it out... I will fill all the remaining places and test it, then I will apply it to the whole sheet with more than 75k rows ...

  7. #7
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: Find multiple words and replace the adjacent column with one word

    Quote Originally Posted by ic3cold View Post
    hi tom, in fact I'm using Excel 2007 ... but posted in XLS, because I saw others used that format ...iferror is command for Excel 2007?
    Yes IFERROR is Excel 2007 and beyond and is cleaner/shorter than IF(ISERROR). I have updated the sample spreadshet with IFERROR.

    Regards,

    Tom
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1