Formula to collect column names if there is a "1" present

Natalia Kataoka

I have a pivot table that I am attempting to use in order to collect a list of all jobs that our subcontractors have/are working on. Basically, I want a roll up of all jobs that each sub has worked on. The sheets where this info is held are all on individual sheets, which is the reason I used a pivot table to try and get everything on a single sheet.

The rows are each subcontractor and the columns are the names of my jobs If a sub is working on that job, the column will have a "1". I assume I need some kind of Join(Collect( formula, but unsure how to snag the name of the columns without creating 100+ extra 'helper' columns.

This is what I am looking for the formula to do:

Ideas?

Paul Newcome

You would need to create a helper row where the jobs (column names) were listed out. Then you would be able to use a JOIN/COLLECT to pull them.

If you had the helper row on row 1, then the formula would look something like this...

=JOIN(COLLECT([1st Job Column]1:[5th Job Column]1, [1st Job Column]@row:[5th Job Column]@row, @cell = 1), ", ")

Paul Newcome

You would need to create a helper row where the jobs (column names) were listed out. Then you would be able to use a JOIN/COLLECT to pull them.

If you had the helper row on row 1, then the formula would look something like this...

=JOIN(COLLECT([1st Job Column]1:[5th Job Column]1, [1st Job Column]@row:[5th Job Column]@row, @cell = 1), ", ")

Natalia Kataoka

That worked, thanks for the help!

Paul Newcome

Happy to help. 👍️

Mike Rini

@Paul Newcome is there a way to do something similar without a pivot table? I completely see how this works for this solution, but I actually have one sheet for our employees which has all of our clients in it.

What I want to do is have a cell that shows me the clients (column headers) that each employee works on (value in the cell). Below is what I've been able to do with your formula above (Employee 1), but I'm looking for the output to be like Employee 2 (that was manually typed in)

Natalia Kataoka

@Mike Rini Take a look at the answer I accepted. You need a "helper row" that duplicates all of our column headers. You should be able to use the exact formula I did with this one extra row. You cannot pull the column header names into the All Clients cell.

Mike Rini

@Natalia Kataoka I know I'm missing something on the "helper row" front. I used the formula above, and that's how I ended up with the 1, 1, 1 in the All Clients column. I think I'm missing how the "helper row" grabs the Client name. Is it just that I literally need to add a row that mimics the column header? In other words, before Employee 1, have a row that says Client 1, Client 2, etc. that I can reference?

Natalia Kataoka

@Mike Rini Correct, what you are "collecting" are the Clients, so those need to be listed out in their own row so it shows in your All Clients cells as "Client1, Client2" etc.

See below screenshot of how my formula works, I pulled out all of the job numbers from all of the column headers and placed in Row 1. You will 'collect' these.

Note that I have a bunch of columns hidden, but you will Collect the entire Row 1

Mike Rini

@Natalia Kataoka Ahh, totally makes sense now. I didn't totally get the "helper row", but it completely makes sense now. Thank you!

Natalia Kataoka

@Mike Rini You're welcome!

Paul Newcome

@Mike Rini Glad you were able to get it working.

@Natalia Kataoka Thanks for helping out with the explanation! 👍️

steve111

What happens if you resort or filter the list, will the data in the String column change?

Paul Newcome

@steve111 Filters will not make a difference. Resorting will only make a difference if you are rearranging the columns.

steve111

Thank you. I see how it will work.