Creating a list without duplicates from data on another sheet

[Losguapos1]

Good evening,

I have a spreadsheet that has a set of data on it and I need to create a list of names from that data. As long as the students have a "1" from any teacher on a given day their name appears on the list for that day. Even if they have more than one "1" they only appear on the list once.

I have attached a sample worksheet.

I need the simplest way to do this. I am not an excel guru so any help would be greatly appreciated.

George

[Losguapos1]

Quite stuck here, hope someone can give me some guidance

George

[everlearning]

George,
I do not know how to attach a document in this forum, I am new to the forum, otherwise I would have attached the solution sheet for you, sorry! But let me try to explain in text form below.

Please try this in a "New List" sheet first.

In the "New List" sheet

1. For Monday in cell A3 enter the formula =IF(SUM(Data!C3:I3)>0,Data!$A3,"")
2. For Tuesday in cell B3 enter the formula =IF(SUM(Data!J3:P3)>0,Data!$A3,"")
3. For Wednesday in cell C3 enter the formula =IF(SUM(Data!J3:P3)>0,Data!$A3,"")
4. For Thursday in cell D3 enter the formula =IF(SUM(Data!J3:P3)>0,Data!$A3,"")
5. For Friday in cell E3 enter the formula =IF(SUM(Data!J3:P3)>0,Data!$A3,"")

6. Now copy Row 3 down to however many students are there in the class (in this case up to row 30)

You will see what you want to see however, the students will always appear on their own rows (i.e.: Student 7 will always be on Row 9, Student 13 will always be on row 15 and so on.)

Is this too simplistic for you? If you really want to see the students neatly lined up like you showed on the "Lists" sheet in your Sample Data.xlsx file then I will have to do more work.

Hope this will work for you.

/el

[AlKey]

Please see attached file

[azumi]

Please check the files

Hope it work


Azumi

[Losguapos1]

Azumi

I am unable to trasnfer your formula to the correct sheet and still have it work. I am going to attach the actual file. It will be the sheets labeled GR 7-8 homework (where data is stored) and sheet 1 (where the list needs to be created).

I cannot for the life of me get this to work

Just to recap what i am looking for: I have a spreadsheet that has a set of data on it and I need to create a list of names from that data. As long as the students have a "1" from any teacher on a given day their name appears on the list for that day. Even if they have more than one "1" they only appear on the list once.

Thanks for all of your help

George

[everlearning]

George,

Did you try what I replied before:
See the "sheet titled "New List" in the attached solution. Does that work for you?
===================================================
Please try this in a "New List" sheet first.

In the "New List" sheet

1. For Monday in cell A3 enter the formula =IF(SUM(Data!C3:I3)>0,Data!$A3,"")
2. For Tuesday in cell B3 enter the formula =IF(SUM(Data!J3:P3)>0,Data!$A3,"")
3. For Wednesday in cell C3 enter the formula =IF(SUM(Data!J3:P3)>0,Data!$A3,"")
4. For Thursday in cell D3 enter the formula =IF(SUM(Data!J3:P3)>0,Data!$A3,"")
5. For Friday in cell E3 enter the formula =IF(SUM(Data!J3:P3)>0,Data!$A3,"")

6. Now copy Row 3 down to however many students are there in the class (in this case up to row 30)

You will see what you want to see however, the students will always appear on their own rows (i.e.: Student 7 will always be on Row 9, Student 13 will always be on row 15 and so on.)

Is this too simplistic for you? If you really want to see the students neatly lined up like you showed on the "Lists" sheet in your Sample Data.xlsx file then I will have to do more work.

Hope this will work for you.

/el

[AlKey]

Hi George,

I have transplanted Azumi's formula to your new workbook. I was unable to test it since there is only one student that has 1 on GR 7-8 homework. I think it should work fine. If you to make any changes to the formula please remember that this an array formula and must entered for use Ctrl+Shift+Enter.

[Losguapos1]

el/ thanks for the input , I did look at it but i need the list condensed and with no spaces

[Losguapos1]

Alkey

thank you thank you thank you!!!

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).