+ Reply to Thread
Results 1 to 7 of 7

Advance searching with cells

  1. #1
    Registered User
    Join Date
    07-27-2017
    Location
    UK
    MS-Off Ver
    2012
    Posts
    43

    Advance searching with cells

    Hi All,

    I'm relatively new to this but this problem really has me stumped. I'll try and describe it best I can and any help would be greatly appreciated.

    Search a large data range in a worksheet and if any cell matches a specific combination of upper and lower letters, then output the contents of the cell adjacent to each. The combination of letters will always be 3 upper case letters (~24 combinations) followed by one or more lower case letters in any combination (a to t). Currently, I’m using the following formulas as arrays to extract the row numbers for each matching cell found, followed by the associated column numbers.

    In this example, the first three upper case letters to search are in cell A1 and the lower case letters are in B1 which have been "ANDed" together in the formulas below, and the data to search is in the range A2 to W100.

    To get the rows that match:
    {=SMALL(IF($A$2:$W$100=$A$1&$B$1,ROW($A$2:$W$100)),ROWS($BA$2:BA2))}

    To get the columns:
    {=SMALL(IF($A$2:$W$100=$A$1&$B$1,IF(ROW($A$2:$W$100)=BB2,COLUMN($A$2:$W$100))),COUNTIF($BB$4:BB4,BB4))}

    and then using an indirect function with an offset copied down to output the results.
    =INDIRECT(ADDRESS(BA2,BB2+1))

    This works ok but I need $A$1&$B$1 in the above to be variable where it will look for 3 upper case letter and ANY combination of lower case letter.

    I.e. some cells contain: PJMa, PJMab, PJMbc, SIEa, SIEb, SIEabc, PJMac, PJMb. So searching for "PJM" and "a" will find PJMa, PJMab, and PJMac. Hope that make sense..

    Thanks

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advance searching with cells

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    07-27-2017
    Location
    UK
    MS-Off Ver
    2012
    Posts
    43

    Re: Advance searching with cells

    Ok thanks, sample workbook attached.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advance searching with cells

    Complex formula (based on function 4)

    =IFERROR(INDEX($I$6:$I$11,SMALL(IF((LEFT($H$6:$H$11,3)=$E$34)*((ISNUMBER(SEARCH($M$6,$H$6:$H$11)))+(ISNUMBER(SEARCH($M$7,$H$6:$H$11)))+(ISNUMBER(SEARCH($M$9,$H$6:$H$11)))),ROW($B$6:$B$11)-ROW($B$6)+1,""),ROWS($B$6:$B6))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-27-2017
    Location
    UK
    MS-Off Ver
    2012
    Posts
    43

    Re: Advance searching with cells

    Many thanks, it looks like its almost doing what I want but my poor description hasn't helped.

    In your amended sheet, cell G18 contains:

    =IFERROR(INDEX($C$6:$C$11,SMALL(IF((LEFT($B$6:$B$11,3)=$E$18)*(ISNUMBER(SEARCH($E$19,$B$6:$B$11))),ROW($B$6:$B$11)-ROW($B$6)+1,""),ROWS($B$6:$B6))),"")

    I'd like this to search the whole data range based on inputs from E18 and E19 so I've changed it to this...

    =IFERROR(INDEX($B$6:$I$11,SMALL(IF((LEFT($B$6:$I$11,3)=$E$18)*(ISNUMBER(SEARCH($E$19,$B$6:$I$11))),ROW($B$6:$B$11)-ROW($B$6)+1,""),ROWS($B$6:$B6))),"")

    But that doesn't seem to work?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advance searching with cells

    It won't work unless the column (as well as row) is defined.

    I have no idea why the data is organised as it is but it would much better with just two columns only (code/document).
    Last edited by JohnTopley; 08-07-2017 at 07:43 AM.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Advance searching with cells

    Added sample in Sheet2: just change D2 for example.
    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)

Similar Threads

  1. unique values advance filter on visible cells
    By johnny_p in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2015, 07:27 AM
  2. To fill the multiple values in multiple cells with one code.thanks in advance
    By boddulus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-28-2014, 03:35 AM
  3. Extracting data with advance filter only from range of cells that have value
    By Pilot5000 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-19-2013, 10:28 AM
  4. VBA code need for advance filter non blank cells, paste data to another sheet
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2012, 12:15 PM
  5. Macro for Save and Advance Invoice changes colors of cells!
    By emartin1029 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2012, 12:20 PM
  6. Extracting data based on a cells value or advance filter?
    By Geniusduffer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-15-2011, 01:17 PM
  7. Foolproof Advance Filter criteria - blank cells
    By Seler Naciowy in forum Excel General
    Replies: 10
    Last Post: 12-01-2008, 11:24 AM

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