Reduce Several Columns of Data to Two Columns

Thank you for considering my problem. I am fairly new to Excel and have been unable to solve it. I have attached a spreadsheet to illustrate it.

The initial data will consist of 18 columns of data, with numbered headings, beside various names in the left hand column. There can be any number of rows. Some of the columns will have a "1" in them, but those columns will have only one "1."

From that data, I would like to summarize it with two columns: One column with the headings of those columns with a "1" and one column with the corresponding name. The headings of columns without a "1" will not appear.

It's difficult to describe the problem clearly, so hopefully the attached example will make it clear. Is there a macro or possibly formulas or array formulas that can accomplish this?

Any thoughts would be greatly appreciated. Thank you.

Here's one way:

U2 =IFERROR(INDEX($2:$2,SMALL(IF(B$3:S$10=1,COLUMN(B$3:S$10)),ROWS($1:1))),"") Ctrl Shift Enter

V2 =INDEX(A:A,MIN(IF((B$2:S$2=U2)*(B$3:S$10=1),ROW(B$3:S$10))))&"" Ctrl Shift Enter

Thank you! Both answers work beautifully. I will have to study the pivot table approach.

I have an additional question. Rather than finding the rows equal to "1", is it possible to find the rows that equal the value in the top row? I have tried to change the array formula you posted but can't get it to work. I am attaching another file to illustrate. Thank you again.

Try these:

U2 =IFERROR(INDEX($2:$2,SMALL(IF(B$3:S$10=B$1:S$1,COLUMN(B$3:S$10)),ROWS($1:1))),"") Ctrl Shift Enter

V2 =INDEX(A:A,MIN(IF((B$2:S$2=U2)*(B$3:S$10=B$1:S$1),ROW(B$3:S$10))))&"" Ctrl Shift Enter

Perfect! Thanks again.

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.