search data and display colunm headers

[c_diver]

I work with Boy Scouts. They have lots of requirements to earn badges. I
have a spreadsheet which lists a summary of requirements in the first column
and the boy’s names in the first row (column headers). When a boy completes
the requirement, I put a "1" in the cell where the name/reqrmnt intersect. I
need to create a separate report that lists the full requirement and lists
names of boys who have NOT completed requirements. So, I need a formula to
search the summary spreadsheet and retrieves the boys names (column header)
who have not completed a requirement. Ideally, all names would be returned
to a single cell however, they could be returned to a row of cells
Any help or pointing me in the right direction would be sincerely appreciated.

 

[T. Valko]

Try this...

Names in the range B1:F1
Requirements in the range A2:A5

For your summary table the requirements are listed in the range A10:A13

Enter this array formula** in B10:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$F2,""),INDEX($B$1:$F$1,SMALL(IF($B2:$F2="",COLUMN($B1:$F1)),COLUMNS($B10:B10))-MIN(COLUMN($B1:$F1))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down to B13 then across until you get a full column of blanks.

 

[c_diver]

Biff - Brilliant! I tried to follow the logic - what a mind twister! But,
it works great! Thank you!

If it's not too much trouble, there is a minor thing that would sure make it
easier to maintain. Most months, we have new kids coming in and some kids
graduating to the next age group, I wanted to have some blank column headers
(no boys names) so I could add kids as they move in without modifying the
equation throughout the spreadsheet every time. I tried extending the name
range in your formula to include several blank columns beyond the last boy.
For example, the name rage you used was B1:F1, so I changed this to B1:I1 but
columns G,H,I did not have boys names entered. The result returns zeros to
the cells where columns without boys names were evaluated . Would there be a
simple way to not have the zeros show up?

Thank you again!

 

[T. Valko]

The easiest way to fix that is to wrap the INDEX function inside the T
function:

=IF(COLUMNS($B10:B10)<=COUNTIF($B2:$I2,""),T(INDEX($B$1:$I$1,SMALL(IF($B2:$I2="",COLUMN($B1:$I1)),COLUMNS($B10:B10))-MIN(COLUMN($B1:$I1))+1)),"")

 

[c_diver]

Everything works! You are the Excel master!

Thank you very much for taking your time to help a total stranger out on this.

Jeff

 

[T. Valko]

You're welcome. Thanks for the feedback!