Combining rows and columns where there's a non-zero cell
I have a table in google sheets which is concerned with role/group names. These are sometimes shared, sometimes not.
I need to use only those names which belong to the relevant cloud service - and will then be using that in a cross table against positions within the organisation (such that one can identify what positions within the organisation should hold what roles/group memberships).
There may well be roles that are unassigned, or cloud services that have no roles.
My thought is to either return one column of concatenated text (GitHub: Backend, etc) - or two have two columns, accordingly.
| Role Name | VPN | Atlassian | DropBox | |
|---|---|---|---|---|
| Super Admin | 0 | 0 | 1 | 0 |
| Groups Admin | 0 | 0 | 1 | 0 |
| User Management | 0 | 0 | 1 | 0 |
| Help Desk Admin | 0 | 0 | 1 | 0 |
| Services Admin | 0 | 0 | 1 | 0 |
| Storage Admin | 0 | 0 | 1 | 0 |
| Inventory Admin | 0 | 0 | 1 | 0 |
| User | 0 | 1 | 1 | 0 |
| Staff | 0 | 0 | 0 | 0 |
| Root | 0 | 0 | 0 | 0 |
| Backend | 1 | 0 | 0 | 0 |
| Frontend | 1 | 0 | 0 | 0 |
| Project Management | 1 | 0 | 0 | 0 |
| Admins | 1 | 0 | 0 | 0 |
| Users | 1 | 0 | 0 | 0 |
| Org Admin | 0 | 1 | 0 | 0 |
| Site Admin | 0 | 1 | 0 | 0 |
| User Access Admin | 0 | 1 | 0 | 0 |
| Team Admin | 0 | 0 | 0 | 1 |
| Member | 0 | 0 | 0 | 1 |
Ideally I want a self-maintained (single cell) formula. It will need to preserve identity with the addition of new columns and new rows, along with the removal of same, so that we do not lose coherence elsewhere.
Something like the following would be a great start
| CloudRole |
|---|
| Google: Super Admin |
| Google: Groups Admin |
| ... |
| DropBox: Member |
Though some way of being able to ensure some form of referential integrity would be even better.
You can use:
=ARRAYFORMULA(TOCOL(IF(B2:E,B1:E1&":"&A2:A,),1))
