+ Reply to Thread
Results 1 to 20 of 20

How to search for PARTIAL matches in this already complicated formula???

  1. #1
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    How to search for PARTIAL matches in this already complicated formula???

    I'm working in multiple tabs, which I'm trying to connect. This is the situation:

    Sheet A - Total overview of projects that are in production within a company
    - relevant data: column B lists all projects; column C lists the employee thats working on the project mentioned in B.

    Sheet B to Sheet D - Employees that are working on above projects. Each employee has their own sheet.
    - relevant data: column A contains the following formula, which retrieves all projects the employee is working on according to sheet A:

    =IF(NOT(ISNA(INDEX(SheetA!$B$2:$B$27;SMALL(IF(EXACT("John";SheetA!$C$2:$C$27);ROW(SheetA!$C$2:$C$27)-ROW(SheetA!$C$2)+1);ROW(SheetA!1:1)))))=TRUE;INDEX(SheetA!$B$2:$B$27;SMALL(IF(EXACT("John";SheetA!$C$2:$C$27);ROW(SheetA!$C$2:$C$27)-ROW(SheetA!$C$2)+1);ROW(SheetA!1:1)))));"")

    So I figured this formula out, which works perfectly, but I've encountered a problem. If a project is handed over to a different employee and so a different name is entered in Sheet A, the project disappears from the first person to have worked on it. I want to be able to see all projects an employee has worked on, even when it's handed over to someone else. So, I figured I could fill names in Sheet A for example as "John + Rose" and change the formula to finding a partial match instead of an exact match. So whenever the name John is found somewhere in the data, regardless of the cell saying + Rose, it would still retrieve the project for John. But I can't figure out how to change the formula to do this.

    Can anyone please help me? My brain isn't working anymore by now...

    Thanks!

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: How to search for PARTIAL matches in this already complicated formula???

    Attach a sample workbook. 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.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: How to search for PARTIAL matches in this already complicated formula???

    1. That seems overly complicated, Im sure that can be simplified a bit, even with google sheets.

    2. If an EE's name is replaced on the project, how would excel know that name still needs to be linked? Im thinking you would need to have another column that shows "past" project workers>
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    I'm working in a google spreadsheet, (mandatory by my company unfortunately), so I can't upload a sample workbook..

  5. #5
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    Is it not possible to adjust the formula to search for partial matches on a name, instead of exact matches?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: How to search for PARTIAL matches in this already complicated formula???

    If you are currently searching for "John" and you want to search for John Smith, use "John*"
    If you want Mr John Smith, use "*John*"

  7. #7
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    Excel would still be able to know someone is linked if I add the second name to the first in Sheet A; for example "John + Rose". If the formula can then search on partial matches for "John" in John his sheet and the formula would search for partial matches for "Rose" in Rose her sheet, the project would still pop up in both sheets, instead of it disappearing from John and appearing at Rose her sheet. Right?

  8. #8
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    This isn't working.. I want to search for John within data such as "John" or "John + Rose" or "Rose + John" and if John is found in a cell in column C (resources), than it should return the project that is mentioned in the same row in column B. I've tried applying the "*" to the name that needs to be searched, but it doesn't seem to work.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: How to search for PARTIAL matches in this already complicated formula???

    That was an ARRAY formula, are you still using CSE to enter?
    What answer did you get?

  10. #10
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    Yes, it returned nothing, which is the return in the formula when the name has not been found in the array. (I preferred to see a blank cell instead of #N/A in case nothing is found)

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: How to search for PARTIAL matches in this already complicated formula???

    For a start, that formula can probably be reduced to this
    =IFerror(INDEX(SheetA!$B$2:$B$27;SMALL(IF(EXACT("John";SheetA!$C$2:$C$27);ROW(SheetA!$C$2:$C$27)-ROW(SheetA!$C$2)+1);ROW(SheetA!1:1)))));"")

    What is the EXACT doing? Try removing that (also adjusted teh formula slightly)...
    =IFerror(INDEX(SheetA!$B:$B;SMALL(IF("John"=SheetA!$C$2:$C$27);ROW(SheetA!$C$2:$C$27));ROWs(SheetA!$A$1:A1)))));"")

  12. #12
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    Thanks, this is a lot less complicated indeed and works the same. The last suggestion doesn't work though, I don't really understand why I need the EXACT, but it was the only way I could get the formula to work. If I remove it, it returns an empty cell instead of a project.

  13. #13
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    Please find attached the test Excel. Hopefully this will help!
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    I've just uploaded a test Excel, I downloaded it from Google Spreadsheets.

  15. #15
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: How to search for PARTIAL matches in this already complicated formula???

    Try

    B3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...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.

    Note I Have fixed it according your sheet name

  16. #16
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    Thanks, how do I activate the array on a Macbook? I thought using CMD+ENTER, but it doesn't work. Or is this different when using a Google spreadsheet?

  17. #17
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: How to search for PARTIAL matches in this already complicated formula???

    Command+Shift+Enter try

  18. #18
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    Never mind, found it. But it isn't working, no results. No error, just no data is returned.

  19. #19
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: How to search for PARTIAL matches in this already complicated formula???

    Check the attached file.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    06-08-2017
    Location
    The Netherlands
    MS-Off Ver
    Google spreadsheets (mandatory to work with at my office unfortunately)
    Posts
    12

    Re: How to search for PARTIAL matches in this already complicated formula???

    Can't check on this computer, don't have Excel at our company. But I will definitely check it tonight when I get home. Will get back to you tomorrow on this! Thanks so much for the help and your time!

+ 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. [SOLVED] Can a Partial Search Array Formula for Multiple return values be looped?
    By RiChCh in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-19-2016, 07:44 PM
  2. Formula/vba to search partial strings in other sheet
    By kevivu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-28-2015, 05:55 AM
  3. Formula that recoginizes partial text matches and concatenates them
    By Shadefalcon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-02-2014, 04:11 PM
  4. [SOLVED] Building a formula to search for partial text
    By macross in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2012, 01:17 PM
  5. [SOLVED] [b]Possible to extract this complicated partial data in Excel?[b]
    By deepz12 in forum Excel General
    Replies: 8
    Last Post: 08-14-2012, 12:10 PM
  6. Replies: 0
    Last Post: 08-11-2012, 03:25 PM
  7. Need Help with a complicated Search/ IF formula
    By RFitzgerald in forum Excel General
    Replies: 3
    Last Post: 05-26-2012, 01:02 PM

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