+ Reply to Thread
Results 1 to 12 of 12

searching with wildcards on variables instead of a constant string

  1. 06-03-2012, 10:59 PM #1
    I_need
    I_need is offline
    Registered User
    Join Date
    05-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    43

    searching with wildcards on variables instead of a constant string

    The below formula works a treat for me when B1 has the value of "AB12CDE34F"

    =INDEX('Data Entry - Blast'!$A$8:$BR$65000,IF(B1=Data Entry - Blast'!$A$8:$A$65000,ROW('Data Entry - Blast'!$A$8:$A$65000)-MIN(ROW('Data Entry - Blast'!$A$8:$A$65000))+1,""),COLUMN(A1))

    However, my B1 value is really made up of the following: C1 value of "AB", D1 value of "12", E1 value of "CDE", F1 value of "34", G1 value of "F". I want to be able to search by using wildcards like this but it doesn't seem to work.

    =INDEX('Data Entry - Blast'!$A$8:$BR$65000,IF(C1&"??"&E1&"*"=Data Entry - Blast'!$A$8:$A$65000,ROW('Data Entry - Blast'!$A$8:$A$65000)-MIN(ROW('Data Entry - Blast'!$A$8:$A$65000))+1,""),COLUMN(A1))
    Reply With QuoteRegister To Reply
  2. 06-04-2012, 07:57 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: searching with wildcards on variables instead of a constant string

    Try:


    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 06-04-2012, 06:29 PM #3
    I_need
    I_need is offline
    Registered User
    Join Date
    05-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: searching with wildcards on variables instead of a constant string

    Thanks NBVC! That modification works.

    However, this is kinda embarassing! :/ I have just realised that my formula is pretty screwy as currently it will only display a result if the condition I selected matches the value of A8 of the Data Entry - Blast spreadsheet. When i changed my formula range to "Data Entry - Blast'!$A$9:$A$65000" then it will only display a result if the condition matches the value of A9.

    Anyone knows what's wrong and how to fix this please?
    Reply With QuoteRegister To Reply
  4. 06-05-2012, 08:06 AM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: searching with wildcards on variables instead of a constant string

    Try this version:

    Please Login or Register  to view this content.
    You will need to confirme this formula holding the CTRL+SHIFT+ENTER keys down, not just ENTER so you get { } brackets around the formula... then copy down until you get blanks or past. This will extract consecutively matching rows.
    Reply With QuoteRegister To Reply
  5. 06-05-2012, 05:58 PM #5
    I_need
    I_need is offline
    Registered User
    Join Date
    05-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: searching with wildcards on variables instead of a constant string

    Hi NBVC. The new code just picks up everything from my dataset in the exact order. It won't even take the match criteria into consideration. :/
    Attached Files Attached Files
    Last edited by I_need; 06-06-2012 at 06:00 PM.
    Reply With QuoteRegister To Reply
  6. 06-06-2012, 08:04 AM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: searching with wildcards on variables instead of a constant string

    I am not sure if you uploaded the correct workbook.. as it doesn't seem to follow the pattern of your initial request....
    Reply With QuoteRegister To Reply
  7. 06-06-2012, 06:01 PM #7
    I_need
    I_need is offline
    Registered User
    Join Date
    05-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: searching with wildcards on variables instead of a constant string

    Sorry, it was the wrong workbook I've now attached the correct one above.
    Reply With QuoteRegister To Reply
  8. 06-07-2012, 08:00 AM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: searching with wildcards on variables instead of a constant string

    Try this.

    Since you say you have a growing number of records, it is best to create a Dynamic Named Range in the Details sheet to accomodate and not to make formula remain as efficient as possible.

    so in the ID Details sheet, go to Formulas tab, then Define Name, enter a name like: Details and then in the refers to field, enter formula: 

    Please Login or Register  to view this content.
    Then back in the Search sheet enter formula in A15:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER and copied across and down.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 06-07-2012, 06:25 PM #9
    I_need
    I_need is offline
    Registered User
    Join Date
    05-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: searching with wildcards on variables instead of a constant string

    Would this code still work if ID Details sheet is actually in another workbook? What are the rules around referencing the Name Range of another workbook? Thanks.
    Reply With QuoteRegister To Reply
  10. 06-08-2012, 08:07 AM #10
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: searching with wildcards on variables instead of a constant string

    Your other workbook must be open to used named range from over there, here's how: http://www.contextures.com/xlDataVal05.html#Dynamic

    ... otherwise stick to fixed ranges, but minimize the number of rows included to max needed.
    Reply With QuoteRegister To Reply
  11. 06-10-2012, 09:14 PM #11
    I_need
    I_need is offline
    Registered User
    Join Date
    05-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: searching with wildcards on variables instead of a constant string

    OMG Now it works!!!! Thank you so much.
    Last edited by I_need; 06-10-2012 at 10:49 PM.
    Reply With QuoteRegister To Reply
  12. 06-12-2012, 01:55 PM #12
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: searching with wildcards on variables instead of a constant string

    From PM:

    Quote Originally Posted by I_need
    Sorry to PM you but can you please help me modify your code below to pull out only 3 most recent rows that match the criteria? The date value is in Column "D" of the ID Details tab.

    =IFERROR(INDEX('ID Details!$A$1:$BV$5000,SMALL(IF(ISNUMBER(SEARCH($B$7&"??"&$B$11&"*",INDEX('ID Details!$A$1:$BV$5000,0,1))),ROW(INDEX('ID Details!$A$1:$BV$5000,0,1))-MIN(ROW(INDEX('ID Details!$A$1:$BV$5000,0,1)))+1),ROWS($A$1:$A1)),COLUMN(A1)),"")

    Thank you in advance.
    If you change the SMALL to LARGE, you will get last match first and descending from there. Copy down only 3 rows to get last 3:

    e.g.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1