none
Multilevel "If Text.Contains" RRS feed

  • Question

  • Hi guys,

    complicated question today.

    I want to run 2-level "If Text.Contains".

    Here is my scenario:

    • Values in column A:
    • a0
    • b1
    • c2
    • Formulas in column B:
    • If Text.Contains([A],"a") then "A" else
      If Text.Contains([A],"b") then "B" else
    • If Text.Contains([A],"c") then "C" else
    • If Text.Contains([A],"0") then "0" else
      If Text.Contains([A],"1") then "1" else
    • If Text.Contains([A],"2") then "2" else "Generic"
    • Formulas in Column C:
    • ????

    In Column C, I want to use similar set of formulas as in column B but I need to always "skip" the first found match. So for A0 row,  I would see A0 in column A, A in column B, and 0 in columns C.

    Any tips?

    Thx!

     



    • Edited by Daniel Zrust Wednesday, February 17, 2016 3:49 PM
    Wednesday, February 17, 2016 2:29 PM

Answers

All replies

  • What is your overall goal? You have one column in your worksheet and want to see a table that contains what?

    Your formulas in column B wouldn't work as they stand here: elses missing. Have the impression that there must be a much easier way.


    Imke Feldmann TheBIccountant.com

    Wednesday, February 17, 2016 3:32 PM
    Moderator
  • sorry, yes "else"s are missing there.

    The deal is that I have about 100 + 100 "if contains" functions generated. Every set of these functions ends with else "Generic".

    I run 1st hundred through column A. Results of this are stored in Column B. Then I run 2nd hundred again through column A. Results are stored in Columns C.

    Some rows from the first run are resulting as "Generic", both in column B and C. Some as something+something. Some as "Generic"+something.

    For combinations which return "Generic" in Col B and "something" in Col C, I want to run next round of checks.

    This time, I would run 2nd set of those 100 "if contains" through Col A and then I would like to run 2nd set of those 100 if contains through Col A again while skipping the previously found value. if I dont skip the previously found value I would see something like something from 2nd 100 + something from 2nd 100. The two found values would be the same.

    The goal is to minimize "Generic"+"Generic" combinations while I know that rules from 2nd 100 can help me achieve that. In these cases, I also need to take care of results from 2nd 100 since I dont want to see something from 2nd 100 + something from 2nd 100 but rather something from 2nd 100 + something v2 from second 2nd

    Does it make sense? 


    The trouble is that the order of if contains matters a lot for me.

    DZ




    • Edited by Daniel Zrust Wednesday, February 17, 2016 3:49 PM
    Wednesday, February 17, 2016 3:43 PM
  • Maybe I would understand it a little if the first checks worked like filters: if Text.Contains([A], "a")=true then "in" else "out" then filtering on "in" aso

    But I seem to miss the big picture here. Sorry, cannot help without visualising the overall goal here.


    Imke Feldmann TheBIccountant.com

    Wednesday, February 17, 2016 3:55 PM
    Moderator
  • Ok, let's try it with "real data". Table with one row.

    column A: "big beautiful boat"

    Vehicles: column B:

    If text.contains([A],"car") then "Car" else

    If text.contains([A],"bus") then "bus" else "Generic"

    =>"Generic"

    Attributes:  column C:

    If Text.Contains([A],"big") then "Big" else

    If Text.Contains([A],"small") then "Small"

    If Text.Contains([A],"beautiful") then "Beautiful" else "Generic"

    =>"Big

    Results of these operations would be "Generic" + "Big" but I want to see "Big" + "Beautiful" if we figure out "multilevel if contains" :)

    Does it make sense?

    DZ

      


    • Edited by Daniel Zrust Wednesday, February 17, 2016 4:05 PM
    Wednesday, February 17, 2016 4:04 PM
  • Better :-)

    So the max number of returned found values will always be 2?

    if it would be "big beautiful Car" you expect "Car" + "big"?

    if it would be "big beautiful Carport" you expect "Car" + "big"?

    What if "Carport" comes later in the search terms? It would be ignored then because there are already 2 matches?

    Shall the matches be case sensitive?


    Imke Feldmann TheBIccountant.com

    Wednesday, February 17, 2016 4:36 PM
    Moderator
  • Better :-)

    So the max number of returned found values will always be 2? YES

    if it would be "big beautiful Car" you expect "Car" + "big"? YES

    if it would be "big beautiful Carport" you expect "Car" + "big"? YES

    What if "Carport" comes later in the search terms? It would be ignored then because there are already 2 matches? - 

    Not sure what you mean - I am only interested in the "big" + "beautiful" set of rules to run as "multilevel". 

    Shall the matches be case sensitive? Not really, I am running everything lower case anyway.


    Imke Feldmann TheBIccountant.com


    Wednesday, February 17, 2016 4:57 PM
  • Have a look if this example does what you expect. Please have a special check at the "green haired monkey".

    https://onedrive.live.com/edit.aspx?cid=de165ddf5d02daff&page=view&resid=DE165DDF5D02DAFF!23666&parId=DE165DDF5D02DAFF!107&app=Excel

    Can provide explanations on how this works if that's what you need.


    Imke Feldmann TheBIccountant.com

    Wednesday, February 17, 2016 5:31 PM
    Moderator
  • We are getting closer.

    https://drive.google.com/file/d/0BxCBbV34rkcEOHhjTzVTYzhfVnM/view?usp=sharing

    See comments inside.

    I adjusted it to my use case.

    Thank you very much for your help!!

    DZ

    Wednesday, February 17, 2016 7:44 PM
  • ? I thought you had about 100 Levels ?

    Would this be your complete solutions if the other values would turn out (with "generic") as you described in your file or would you consider this as the start to some loops/repeats?


    Imke Feldmann TheBIccountant.com

    Wednesday, February 17, 2016 7:51 PM
    Moderator
  • Well, I will have 100 words to check for in Level1 column and another 100 in Level2 columns. (see the file now, I updated it).

    So we were understanding the term "level" differently...

    If we add generic then that's final :)

    Wednesday, February 17, 2016 7:56 PM
  • Totally sure about the small ugly lorry? both matches come from the Level2!

    Imke Feldmann TheBIccountant.com

    Wednesday, February 17, 2016 8:17 PM
    Moderator
  • If there were 3 matches in Level 2: Which ones to take?

    Imke Feldmann TheBIccountant.com

    Wednesday, February 17, 2016 8:18 PM
    Moderator
  • If there were 2 matches in Level 1 already (and another one in Level 2): Which ones to take?

    Imke Feldmann TheBIccountant.com

    Wednesday, February 17, 2016 8:23 PM
    Moderator
  • Level 1 = only first match and always the word closer to the top

    Level 2 = the word closer to the top has always priority, only 2 matches. => that's why "ugly-small" is correct for me.

    Does it make sense?

    Wednesday, February 17, 2016 8:27 PM
  • Wednesday, February 17, 2016 9:33 PM
    Moderator
  • Seems working, I will test it on few 1000s rows this week. Thank you very much!

    DZ

    Wednesday, February 17, 2016 10:17 PM