Combining rows and columns where there's a non-zero cell

Question

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	Google	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.

z.. · Accepted Answer · 2025-03-04 17:07:01Z

2

You can use:

=ARRAYFORMULA(TOCOL(IF(B2:E,B1:E1&":"&A2:A,),1))

answered Mar 4 at 17:07

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Konchog Mar 4 at 17:29

I'm now wondering about ensuring the ref. integrity also.. I guess I may need to filter instead of using the ignore blanks flat.

doubleunary Mar 4 at 17:53

Those formula results will always be up-to-date when the source data changes. Google Sheets formulas have referential transparency.

Konchog Mar 4 at 18:08

@doubleunary, the point I'm making is that if I am using those values in another subscription, they need to maintain their row position - otherwise the subscription will lose coherence.

doubleunary Mar 4 at 18:36

This question is regarding "Combining rows and columns where there's a non-zero cell". Ask only one question per post. Post a new question instead.

Collectives™ on Stack Overflow

Combining rows and columns where there's a non-zero cell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related