Automating a table from data in another table in Numbers for Mac

This question looks very much like this post.

Here's a one-formula solution that uses two relatively new functions in Numbers:

The formula in A2, filled down, is:

=REGEX.EXTRACT(TEXTJOIN("~",1,Table 1::A:B),"(\w+)~"&A$1&"\b",ROW(cell)−1,1)

You can wrap the formula in IFERROR to hide the red triangles, but the triangles can be useful. For example you can click one and get this information:

Note how the message shows the regular expression that is constructed by the formula. This is used to try to find a match on any "word" characters followed by ~East. The match is sought in the results of the TEXTJOIN, which Numbers holds in memory. If the results of TEXTJOIN were displayed in a cell they would be:

Albert~East~Bob~West~Colby~West~Devon~North~Earl~East~Frank~West~George~East~Harold~East~John~West

The REGEX.EXTRACT parameters are like this:

If your region uses , as the decimal separator then your formula would be:

=REGEX.EXTRACT(TEXTJOIN("~";1;Table 1::A:B);"(\w+)~"&A$1&"\b";ROW(cell)−1;1)

SG

This is doable, just not built in, it is formula based so the data in the second table will change if table 1 does

The idea is to find the first East in table 1 col B, remember that number, use that to do an offset to grab the cell in A for that row.

then find the next EAST down and remember its row number, repeat until there are no more.