PASEL

Q: Extracting text from strings in a single cell from a row of cells

Hi,

 

Is there an easier way to lookup a text / word in a string within row of cells and where a match is found return a header text - see example shown below.

Screen Shot 2016-09-15 at 18.07.04.png

The table on the left contains the original compliance positions in groups by companies. I would like to populate the table on the right with compliance positions shown in the table on the left, using the header text (red box). Example 1 shows that "Company C" is non-compliant in left table and returns a "Non-Compliant" in the relevant cell for company C. The 2nd example Company's A, C, and Z are compliant in the left table and returns "Compliant" in the relevant cell for company's A, C and Z respectively. The same applies for the Partials.

 

The rows must also align as shown as the leftmost column will contain a unique reference (not shown in the example).

 

My current approach was to simply build a small matrix and use IF(COUNTIF($E5,"*Company A*"),"Compliant","") - This works fine, but is a little untidy - The resulting table shown above is really what I would like o achieve.

 

I also attempted to use nested "IFs", "Find", "Index" and "Match" combinations, based on the some early posts, but just could not get the syntax correct.

 

Would be grateful for alternative ways to the build the table on the right.

 

Thank you in advance ...

 

Pasel

MacBook Pro, Mac OS X (10.7.5)

Posted on Sep 15, 2016 10:18 AM

Close

Q: Extracting text from strings in a single cell from a row of cells

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 15, 2016 10:55 AM in response to PASEL
    Level 6 (19,282 points)
    iWork
    Sep 15, 2016 10:55 AM in response to PASEL

    This may work for you:

    Screen Shot 2016-09-15 at 12.54.21 PM.png

     

    A2=IF(COUNTA('Table 1 - Company Compliance Positions'::$A2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$A2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$A2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$A$1, ""), "")&IF(COUNTA('Table 1 - Company Compliance Positions'::$B2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$B2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$B2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$B$1, ""), "")&IF(COUNTA('Table 1 - Company Compliance Positions'::$C2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$C2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$C2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$C$1, ""), "")

     

     

    this is shorthand for… select cell A2, then type (or copy and paste from here) the formula:

    =IF(COUNTA('Table 1 - Company Compliance Positions'::$A2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$A2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$A2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$A$1, ""), "")&IF(COUNTA('Table 1 - Company Compliance Positions'::$B2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$B2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$B2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$B$1, ""), "")&IF(COUNTA('Table 1 - Company Compliance Positions'::$C2)>0,IF(LEN('Table 1 - Company Compliance Positions'::$C2)−LEN(SUBSTITUTE('Table 1 - Company Compliance Positions'::$C2, A$1, ""))>0, 'Table 1 - Company Compliance Positions'::$C$1, ""), "")

     

     

     

    select cell A2, copy

    select cell A2 thru the end of column F, paste

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 15, 2016 11:03 AM in response to PASEL
    Level 6 (19,282 points)
    iWork
    Sep 15, 2016 11:03 AM in response to PASEL

    you can also use:

    A2=IF(IFERROR(FIND(A$1,'Table 1 - Company Compliance Positions'::$A2), 0)>0, 'Table 1 - Company Compliance Positions'::$A$1, "")&IF(IFERROR(FIND(A$1,'Table 1 - Company Compliance Positions'::$B2), 0)>0, 'Table 1 - Company Compliance Positions'::$B$1, "")&IF(IFERROR(FIND(A$1,'Table 1 - Company Compliance Positions'::$C2), 0)>0, 'Table 1 - Company Compliance Positions'::$C$1, "")

     

    fill like before

  • by t quinn,Helpful

    t quinn t quinn Sep 16, 2016 1:51 PM in response to PASEL
    Level 5 (5,053 points)
    Mac OS X
    Sep 16, 2016 1:51 PM in response to PASEL

    Hi Pasel,

     

    Here is my take.

    Screen Shot 2016-09-16 at 11.27.45 AM.png

    A2= IF(IFERROR(FIND(A$1,Table 1::$A2,1),0)>0,Table 1::$A$1,"")&
IF(IFERROR(FIND(A$1,Table 1::$B2,1),0)>0,Table 1::$B$1,"")
&
IF(IFERROR(FIND(A$1,Table 1::$C2,1),0)>0,Table 1::$C$1,"")

    I think this is also what Wayne was aiming for with his second solution.

     

    I want to suggest briefer table names if it is workable for you. Less spaces will make it much easier to parse the formulas.

     

    quinn

  • by PASEL,

    PASEL PASEL Sep 16, 2016 1:27 PM in response to Wayne Contello
    Level 1 (4 points)
    Desktops
    Sep 16, 2016 1:27 PM in response to Wayne Contello

    Hi Wayne,

     

    Thank you for sharing your solutions - Both worked in my example file and successfully transferred to my working file.

     

    I tried something similar to your second solution, but can now see the errors I made with the syntax, which is not my strong point :-)

     

    PASEL

  • by PASEL,

    PASEL PASEL Sep 16, 2016 1:32 PM in response to t quinn
    Level 1 (4 points)
    Desktops
    Sep 16, 2016 1:32 PM in response to t quinn

    Hi Quinn,

     

    Again, thank you for the additional solution - I have made the switch from excel to numbers and have quickly learned that long title names for the tables does make it difficult to following syntax. I like the simplicity of numbers, especially it how colours codes the formula.

     

    PASEL

  • by Wayne Contello,Solvedanswer

    Wayne Contello Wayne Contello Sep 16, 2016 1:34 PM in response to PASEL
    Level 6 (19,282 points)
    iWork
    Sep 16, 2016 1:34 PM in response to PASEL

    I am glad that one of the solutions help you solve your problem.  Do you have any other questions to completely solve this problem?

  • by PASEL,

    PASEL PASEL Sep 16, 2016 1:45 PM in response to Wayne Contello
    Level 1 (4 points)
    Desktops
    Sep 16, 2016 1:45 PM in response to Wayne Contello

    Hi Wayne,

     

    I do not have any questions regarding your solutions. I am sure that I will have more problems with my attempts with syntax that will need help with.

     

    Thank you again.

     

    PASEL

  • by PASEL,

    PASEL PASEL Sep 16, 2016 1:47 PM in response to Wayne Contello
    Level 1 (4 points)
    Desktops
    Sep 16, 2016 1:47 PM in response to Wayne Contello

    Hi Wayne,

     

    I do not have any questions regarding your solutions. I am sure that I will have more problems with my attempts with syntax that will need help with.

     

    Thank you again.

     

    PASEL