Need A Formula to copy Rows Containing Certain Text in One Particular Column into Another Table

HI Andy,

You can do this with a pair of formulas, one creating an index in an added column on the table containing the names, the other in the three columns receiving the information on the second table.

Your data table (Table 1) is on the left. The formula directly below that table is entered in cell E2, and filled down to the last row of that column.

The formula places a serial number in each row where column 4 contains the three letter string "mag"

Table 1::E2: IF(LEN(D2)>LEN(SUBSTITUTE(D2,"mag","")),MAX(E$1:E1)+1,"")

The second table, on the right contains the second formula shown above. entered in cell A2. then filled right to D2, and filled down to the last row of the table.

Table 2::A2: IF(ROW()−1>MAX(Table 1::$E),"",INDEX(Table 1::A,MATCH(ROW()−1,Table 1::$E,0)))

This formula has two parts: The part shown in bold is the core formula, and does the work of copying the data from the indexed rows into the second table.

That core is wrapped in an IF statement that acts as a switch, stopping calculation and placing a null string in rows beyond the largest number in the index.

Regards,

Barry

What I like about Numbers is that you don't have to be a genius to do something like this. You don't need extra columns. You don't even need a formula. You just need a few clicks to apply a filter:

Select the visible cells in the now visible table and command-c to copy to the clipboard. Click once in a cell of an existing destination table and Edit > Paste and Match Style.

Done!

The more complicated formula approach might be better if you need to extract rows from the table several times a minute. But in normal usage the the filter approach is, in my experience, vastly easier and more efficient.

SG

Hi Andy,

Another way.

The SEARCH function is not case-sensitive. It will show the starting position of mag or Mag within a cell in column D.

IFERROR inserts a zero if mag or Mag are not present in a cell in column D.

Sort the table descending by column E.

Your desired rows are now at the top of the table.

Regards,

Ian.

The last bit should be Edit > Paste Formula Results if you have formulas in the cells you are copying into another table.

SG