How does this JOIN/COLLECT formula work in one sheet but not another, mirrored sheet?

WtaylorW

This sheet originally had a formula that would COLLECT and JOIN the column names of every value in a row that was "Fail". It also separates the collected column names by ", " (comma and space). A coworker has changed the formula, and it still works, but only on this sheet. Now I can't remember my original formula, and I've never seen what they have with the "Fail" value being inside the column name brackets. Does anyone have any insight how this works, why only on this sheet, and how I can get the results I need with a similar formula that uses a range and doesn't list each column individually?

The formula:

=JOIN(COLLECT([Lights Fail]@row:[Steering Test Fail]@row, [Lights Fail]@row:[Steering Test Fail]@row, <>""), ", ")

Paul Newcome

It looks like there are hidden helper columns with formulas that output the column names that have failed and then the JOIN/COLLECT grabs the helper columns that are not blank.

Paul Newcome

It looks like there are hidden helper columns with formulas that output the column names that have failed and then the JOIN/COLLECT grabs the helper columns that are not blank.

WtaylorW

You are exactly right! I had no idea helper columns were made, and I spent way too much time figuring that out. I couldn't see past the formula. I couldn't figure out how to delete my question afterwards.

Side note: @Paul Newcome , you are an unsung hero of many peoples' workday.

Paul Newcome

Happy to help. 👍️