I have several tables that represent rooms in our office, each table includes a column of few cells contain workers names and a device named like the following "Device A", "device B" etc.

Table 1:

Table 2:

I want to summarize all their names exclude the device to a cell on a new table.

Table 3:

The issue is that I can change the devices order so the names on the table can change often.

I want to use TEXTJOIN (or other tool) to combine all cells unless the cell includes "Device" in it, without specify exactly which one.

I tried to use REGEXMATCH, VLOOKUP, SEARCH, COUNTIF but they all return a list, so its not working well inside TEXTJOIN.

I've tried to use FILTER too but wildcard doesn't work there so I must provide the full name of the device.

Please your advice, Thanks in advance!

CodePudding user response：

You should be able to do something like

=textjoin(", ", 1, filter(G1:G6, not(regexmatch(G1:G6, "Device"))))

or

=textjoin(", ", 1, filter(G1:G6, left(G1:G6, 6) <> "Device"))

(Include the sheet name if you want the results to appear on a different tab).