Using one ss to grab data from another- showing completion of assignment given to a group

I am a choir director who knows just enough about excel usually to be dangerous (to myself).

I started a project, didn't think it through, and now am paying for it! LIve and learn!

I have a choir of 32 people who have been assigned 20 pieces of music to learn for a trip to a competition in Europe in 6 weeks. They were to report to me that they have learned notes/rhythms. They emailed my assistant and he entered this data to a simple ss.

A - FN
B- LN
C - Piece title
D- notes/rhythm completed

I probably deserve a lecture about not having a web form that people fill in instead of dealing with hundreds of emails - but it's too late for this time.

There are currently about 450 lines in the ss (out of about 600 when everyone has learned their stuff)- since each email from a person, was to report they had finished a single piece, not a record of all of them at once.

What I'd like to end up with is a ss with the following:

Column A - FN
Column B - LN
Columns C - V Each of the next 20 columns contain the title of one of the 20 pieces

Then in - Rows 2-33 of Col A - the names of the people in the choir

My assistant has been careful to type the names or people and the piece titles the same way each time, so I only need to find the actual names of the pieces and people, not variations of them...

I can set that basic spreadsheet up, but I don't know how to do the following -

I'd like to grab the data from the other sheet - searching each row (cols A & B) for the person's name, which might appear in as many as 20 rows, or no rows at all yet.

And then put an x in the correct column under the piece name for the piece that is listed in that row.

I'd also like to have green for the cells which contain an x (completed pieces) and red for those that don't - I know generally how to conditionally format the x's to green and the blank cells to red so I can see in a glance how each piece is doing and who's behind on their task of preparing all 20 pieces.

Thanks in advance for any help - although I know lack of prior planning on my part does not constitute an emergency on your part!

Gary

Hello
Attached is a small example file based on what you've outlined as I understand it. Is this what you are looking to do?

Hope it gives you a few ideas.
DBY

Thanks for the response -
From column C on - that's EXACTLY what I want to do!

I am hoping to Grab the names in Col A and B from the other sheet rather than typing them in - but with only 32 that won't be too hard.... still- It would be great if an alteration to the formula would allow that..

Thanks, again!
Gary

Hello
The best way of creating a unique list of names from your data list is to select both the First and Last Name columns on your data sheet, copy then paste them into your Check Sheet, then while they're still selected, on the Data Tab on the ribbon click 'Remove Duplicates', if all's well click OK on the following dialogue boxes, this will leave you with a unique list of names. Perform the 'Remove Duplicates' on a copy and not the original data for safety!

Remove Duplicates (1).jpg

DBY

DBY - thanks for the easy solution!

I"m up and running with my summary ss!

All Best,
Gary