In this post, we will see how to resolve How do I have Excel create a list of items that have and “X” in an indicated column
Question:
I am trying to find a way in Excel to generate list of people that have read a procedure on a second sheet. In the table we are reading the people who have done it are indicated with an “X” next to their nameFor example (These are not real names):
| Name | Procedure 001 Read | Procedure 002 Read |
|---|---|---|
| Bob Smith | X | |
| Jeff Smith | ||
| Alan Jones | X | |
| Scott Rogers | ||
| Nick Davis | X |
I would want the results to be:
| Procedure 001 | Procedure 002 |
|---|---|
| Alan Jones | Bob Smith |
| Nick Davis |
I tried using lookup and vlookup functions, but I could not figure out how to make them do what I was expecting.
Any suggestions or help is greatly appreciated!
Best Answer:
Using Structured References with table name =Table1For the Header Row:
=SUBSTITUTE(INDEX(Table1[#Headers],1,SEQUENCE(,COLUMNS(Table1)-1,2))," Read","")For the Columns (and fill across as needed)
Results
If you have better answer, please add a comment about this, thank you!
Source: Stackoverflow.com