PASEL
Level 1 (4 points)
Desktops

Q: A Matrix using Index & Match from two separate tables

Hi All,

 

I have an interesting little challenge - I haven't come across anything quite like this before - Hoping that someone in the community has had a similar dilemma and maybe able to help.

 

Attempting (as always) to create a matrix (Table 1 - Matrix) and populate it with the status (e.g. R2, P9, etc.) from another separate table (Table 2 - Lookup). - Ideally, I would like to be able to return contents from a cell, but a logical result such as value 0/1 or “true/false” would also be OK.

 

Screen Shot 2016-09-08 at 17.28.08.png

Screen Shot 2016-09-08 at 17.36.05.png

In table 1, I have highlighted two examples of this:

 

RE-19 is linked to DE-772 and the status is “R2”

RE-29 is linked to DE-113 and the status is “P9"

 

Note: The status column will only ever have a single entry per cell

 

My first approach was to simply use Index and Match. This actually works fine, until the cell contains more than one RE-* reference in comma separated string as shown in these two examples. So, for the following example I need to find another way to say I found the "RE-" reference I am looking for and return the status value:

 

For example,  the value “P9” is what I would like to be returned where the RE-29 reference was found in the corresponding DE-113 entry (E4).  Likewise, the value of R2 to be returned where RE-19 was found in the corresponding DE-772 cell containing a string of RE-* references.

 

Would really welcome any ideas, suggestions or solutions to similar problem. If anyone needs a copy of the example file to play with, please let me know.

 

Thank you in advance.

 

Pasel

 

Additional Notes:

  • Both tables have unique identifiers or references (i.e. RE-* in table 1 and DE-* in the lookup table).
  • Both tables have links between the RE-* (shown in RED frame) and DE-* (shown by Green frame) - It is therefore possible use either
  • Table 2 will increase in size as more entries are added.
  • Most RE-* will have a one to many links in column C

MacBook Pro, Mac OS X (10.7.5)

Posted on Sep 8, 2016 10:00 AM

Close
PASEL

Q: A Matrix using Index & Match from two separate tables

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 8, 2016 12:17 PM in response to PASEL
    Level 6 (19,037 points)
    iWork
    Sep 8, 2016 12:17 PM in response to PASEL

    you can use the function substitute() to replace "RE-??" with "" then check if the length of the new string is shorter than the original.  If the strings are the same then the string "RE-??" was NOT found in the original.  if the new string is shorter, then you know the string "RE-??" was found (and removed)

  • by t quinn,

    t quinn t quinn Sep 8, 2016 9:58 PM in response to PASEL
    Level 5 (5,012 points)
    Mac OS X
    Sep 8, 2016 9:58 PM in response to PASEL

    Hi Pasel,

     

    I am curious about your examples. In your matrix table would DE-2241 be pulling a value from your lookup. Would DE-29 be pulling values for all 13 or so RE- values? Are you wanting to populate Matrix::C also? Will Matrix expand also? What will you do if you want more than 256 columns?

     

    I suggest you have one table as a lookup (data) and one table as a report.

     

    Here is a suggestion based on my understanding of your question.

    Screen Shot 2016-09-08 at 10.45.41 PM.png

    B2= IF(FIND(B$1,INDEX(Lookup::$D,MATCH($A2,Lookup::$B,0),column-index,area-index),1 )>0,Lookup::$C2,"")

     

    This solution relies on the DE- values being in the same order in both tables. If DE-19 is followed by DE-20 in one table and DE-21 in another there will be issues. These can be resolved with an expanded formula.

     

    quinn

  • by PASEL,

    PASEL PASEL Sep 9, 2016 3:23 AM in response to Wayne Contello
    Level 1 (4 points)
    Desktops
    Sep 9, 2016 3:23 AM in response to Wayne Contello

    Hi Wayne,

     

    I understand the principle, but I have never used substitute - Would this be used in netted function with Len? - How would the syntax be assembled? Do have an example of how to do this?

     

    It sounds like an interesting technique.

     

    PASEL

  • by PASEL,

    PASEL PASEL Sep 9, 2016 3:45 AM in response to t quinn
    Level 1 (4 points)
    Desktops
    Sep 9, 2016 3:45 AM in response to t quinn

    Hi T Quinn,

     

    Table 1 is the matrix that I would like to try and populate, but it is only the Plan A. I have two very large tables: the RE table which has around 4,400 row entries (the number of entries in RE table is fixed) and DE table has around 2,800 row entires and this will grow in size DE-* are generated automatically, are unique and sequential.

     

    Both tables have columns that list out the links between RE-* and DE-* entries (highlighted with the RED and GREEN frames - The should correlate exactly depending on which table you start with.

     

    What I would like to do is to set the left most column as the RE-* and transpose the DE-* along the top column (table header) and where there is a link between the DE and the respective RE, then either return as result 0/1 or True / False in the intersecting cell. However, it would be better if the formula would return the "status" value (P9, R2, etc.) into the RE-* to DE-* intersecting cell. This would add another dimension to the matrix. This is only complicated by the cell where an RE-* reference resides is within a string.

     

    The RE-* and DE-* are always ordered in the cells - The number in a string (RE-* or DE-*) could increase as the process progresses.

     

    Not all RE-* will have a link to a DE-* as this is progressive process and eventually there should be at least one DE-* linked to a RE-*

     

    If the 256 columns is a limitation, then I would simply spread this across however many sheets is required and is probably more practically anyway.

     

    I hope this clarifies what I am trying to achieve ...

     

    In the meantime, I will look at your suggestion and let you know how I get on ...

     

    Thank you

     

    PASEL

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 9, 2016 6:24 AM in response to PASEL
    Level 6 (19,037 points)
    iWork
    Sep 9, 2016 6:24 AM in response to PASEL

    you can always get help on functions in Numbers, by typing the "=" (equal) in any cell then use the function browser on the right.  you can enter text in the search field to narrow the list of functions or click groups to simple browse through the list.

     

    the syntax for substitute is:

    SUBSTITUTE(source-string,existing-string,new-string,occurrence)