'TEXTJOIN cells unless a cell contain part of a string
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:
| HR G1:G6 |
|---|
| George |
| Ema |
| Benjamin |
| Johanna |
| Device A |
Table 2:
| Dev H1:H6 |
|---|
| Josh |
| Jenna |
| Tom |
| Jessica |
| Device B |
I want to summarize all their names exclude the device to a cell on a new table.
Table 3:
| Room | NAME A1:B3 |
|---|---|
| HR | George, Ema, Benjamin, Johanna |
| Dev | Josh, Jenna, Tom, Jessica |
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!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
