Formula to collect column names if there is a "1" present
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?
Best Answer
-
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), ", ")
Answers
-
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), ", ")
-
That worked, thanks for the help!
-
-
@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)
-
@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.
-
@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?
-
@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
-
@Natalia Kataoka Ahh, totally makes sense now. I didn't totally get the "helper row", but it completely makes sense now. Thank you!
-
@Mike Rini You're welcome!
-
@Mike Rini Glad you were able to get it working.
@Natalia Kataoka Thanks for helping out with the explanation! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!
