+ Reply to Thread
Results 1 to 4 of 4

Search Dynamic words within Column

  1. #1
    Registered User
    Join Date
    03-18-2012
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    2

    Search Dynamic words within Column

    Search Dynamic words within Column

    Hi all, I am really appreciating your help here.
    I have a data base that contains data, which is exist in sheet “data”, I would like to put the words that I want to search and copy all corresponding rows that match from the “Data”. For Example I put several words at column A within “sheet1”, I want to search all of these words within the “Data” sheet and give me the results of the rows having these words at sheet “Result”
    I appreciate if somebody can help me here, I am attaching here my worksheet
    Attached Files Attached Files
    Last edited by bayatmu; 03-21-2012 at 01:53 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,024

    Re: Search Dynamic words within Column

    I've put this formula in B2 of your Sheet1:

    =IF(A2="","-",IF(ISNA(MATCH(A2,Data!B:B,0)),"-",MATCH(A2,Data!B:B,0)))

    and copied it down beyond your data (actually to row 15 - the hyphens show this).

    Then in your Results sheet I have copied the headings from the Data sheet into row 1, and put this formula in A2:

    =IF(OR(Sheet1!$B2="-",Sheet1!$B2=""),"-",INDEX(Data!A:A,Sheet1!$B2))

    and this one in B2:

    =IF(OR($A2="",$A2="-"),"",IF(INDEX(Data!B:B,Sheet1!$B2)="","",INDEX(Data!B:B,Sheet1!$B2)))

    I have then copied the formula from B2 across to C2:U2, and applied the same formatting as from row 3 of the Data sheet. Then row 2 can be copied down as far as you like in order to ensure that you have picked up all the codes from Sheet1 (I've copied to row 13, as can be seen from the hyphens in column A). And there you have it ...

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-18-2012
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Search Dynamic words within Column

    Man
    This is great, it worked, i apprciate your help

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,024

    Re: Search Dynamic words within Column

    Well, that's good to hear. The important thing is to ensure that the formulae are copied beyond the amount of data that you want to transfer into the results sheet, otherwise you will miss some.

    Perhaps you can mark the thread as Solved (the FAQ tells you how) and click on the "star" icon in the bottom left corner of any post that has helped you, in order to pass on thanks directly.

    Pete

+ 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