Return lines only with one value

[npc5017]

I included below an example inventory table.

In this table, I have 6 individuals and 6 items that they're trying to collect. I want Excel to automatically create a list of the items that an individual has. Is there a way to do this? The actual data that I'm working with has 600 items and growing, so it's getting tedious to manually make the list. I don't want to use filtering because I want to be able to see the lists for all 6 individuals at once instead of filtering the column for each one.

Thanks!

Excel Example.JPG

[Teethless mama]

You will get more help if you attached the workbook not the picture.

[tigeravatar]

What's making the checked checkbox? Is it a specific letter in a special font (like a capital R in the Wingdings 2 font)? If so, a formula can be used to display the results you need.
Or are you using a control checkbox (Form or ActiveX)? In which case VBA might be better suited to the task.

[tigeravatar]

npc5017,

I'm guessing you're using the Wingdings 2 font because it looks exactly like it, so attached is an example workbook based on the criteria you described.
In cell B10 and copied over and down to G15 is this formula:

Please Login or Register  to view this content.

Does that work for you?

[npc5017]

Tiger - I did use Wingdings 2. Sorry, I should have mentioned that. That formula works great, thank you! I'm a newbie at IF statements and arrays and haven't seen the Match or Index commands before, so I'm trying to work through what that all means so I can use it without just copying and pasting your work. It's daunting, but kind of fun! Some of it's clicking and playing around with match and index are helping.

It did raise one question though - How did you set the value of the checkmarks to appear as R and the value of the X's to appear as O?

Teethless mama - Doh! I should have thought of that. Thanks for the advice!

[tigeravatar]

The wingdings font family uses the same keyboard characters as every other font, it just has a different symbol for each alphabet letter, number, and special character. The symbol for R is a checkbox and the symbol for O is an x. I didn't do anything special, just changed the font. If you change the font back to a standard font, like Calibri or Arial, the cells will show R's and O's like normal. Then you could just change it back to wingdings 2 and they'll be checkboxes and x's again.