Sep 05 2018 02:24 PM
Sep 05 2018 02:24 PM
Hello again, wonderful Excel gurus! I need help with writing a formula that extracts (if possible) information from cells that contain multiple data. I have included my examples for you to look at to make my explanation clearer.
I figured out how to write the formula when there is a direct 1:1 match. When I changed part of the data to make it multi-data cells, I now can't figure out what to do.
Summary: I am looking for a match of contract = contract.
Example 1: (I got this part to work)
EmpPref = contract & EmpType = contract = MATCH
Example2: (I can't figure out the formula for this)
EmpPref = contract,any,remote & EmpType = contract = MATCH
Any help is appreciated!
Thanks!
Ronna
Sep 05 2018 02:36 PM
Hi Ronna,
That could be
=IF(ISNUMBER(SEARCH("contract",[@EmpPref2]))*([@EmpType2]="contract"),"MATCH","0")
and attached
Sep 06 2018 07:58 AM
Sergei, thank you very much! I didn't even think of using ISNUMBER and SEARCH so thanks for teaching me something new!
Now, I have a twist on the formula. In Example Table 3 (attached), I am trying to write a formula that says: IF column EmpPref3 says "contract" OR "perm" AND column EmpType says "perm", then return MATCH; otherwise return 0.
What do I need to add to your formula to make this work?
Thanks,
Ronna
Sep 06 2018 08:31 AM
Hi Ronna,
That will be
=IF((ISNUMBER(SEARCH("contract",[@EmpPref3]))+ISNUMBER(SEARCH("perm",[@EmpPref3]))),"MATCH","0")
and attached. Take into account what zero eqs to FALSE and any other number eqs to TRUE in logical operations.
Sep 07 2018 01:10 PM
Sergei, thanks again for your help. I have one last step (I think) that I need help with for this formula and I've also included my sheet for you to look at to see what I'm trying to do.
In Table 3, I am trying to write a formula that says: IF column EmpPref3 says "contract" OR "perm" AND column EmpType3 says "perm", then return 1; otherwise return 0.
My guess at the formula is this: = IF (( ISNUMBER (SEARCH ("contract", [@EmpPref3])) + ISNUMBER (SEARCH ("perm", [@EmpPref3]))) * (( [@EmpType3] = "perm" ), "1", "0")
What am I missing or doing incorrectly? Any help or tips are appreciated!
Thank you!
Ronna
Sep 08 2018 05:25 AM
Ronna, you have correct formula in your post, but in the file formula your forgot to add AND condition
but shall be
=IF((ISNUMBER(SEARCH("contract",[@EmpPref3]))+ISNUMBER(SEARCH("perm",[@EmpPref3])))*([@EmpType3]="perm"),"1","0")
and attached
Sep 12 2018 11:04 AM
Sergei, thank you again for your help! With your help, I am slowly beginning to see how the pieces of my puzzle are coming together.
Ronna :)
Sep 13 2018 08:41 AM
Sergei, I'm closing in on completing my Excel project. What I need help with is that now I have two separate formulas that work for different parts of my overall Excel project. One formula is for static information and the other formula is for dynamic data (or multi-data cells). I want to combine the two formulas to ultimately return the information on the green "Combined Info" tab...the Candidate ID, #ComboMatch (the # of Job Order matches) and JobOrderIDs. I have attached my examples for you to look at to help my questions make sense.
The two working formulas I have are:
Static formula: =COUNTIFS(JOs[Dept],[@Dept],JOs[Relo],[@Relo],JOs[2DigZip],[@2DigZip])+(0.00001*ROW())
Dynamic formula: =(IF((ISNUMBER(SEARCH("in play",'Practice Data 2.xlsx'!CandTable[@Status]))+ISNUMBER(SEARCH("ready",'Practice Data 2.xlsx'!CandTable[@Status])))*('Practice Data 2.xlsx'!JOtable[@Status]="in play"),"1","0"))*(IF((ISNUMBER(SEARCH("contract",'Practice Data 2.xlsx'!CandTable[@EmpPref]))+ISNUMBER(SEARCH("perm",'Practice Data 2.xlsx'!CandTable[@EmpPref])))*('Practice Data 2.xlsx'!JOtable[@EmpPref]="contract"),"1","0"))
How do I combine these two formulas into one large one? I am not sure which is more useful, TEXTJOIN or IF(ISNUMBER(SEARCH....)) or if there is something else I am missing.
Thanks for reading and for your helpful ideas!
Ronna M.