Source
r/excel•
Delicious-Number

Create a list of missing values from multiple columns

solved

I don’t even know we’re to start to look!

I am an infant school teacher so I don’t use excel for anything complicated but I have an idea of something I want to do but I don’t know what it is called - I’m hoping if someone knows what it is that I want to do then I could find a YouTube tutorial to help me! I have had a look at some for pivot tables but that is not quite right!

I have a spreadsheet for the school with all of the children’s names in the first column. The headers for the other columns are the letters which children need to know e.g a, b, c etc. ( there are a lot more than 26 because we have all the digraph a like “ai “ and blends like “scr” which we have to test too)

For child the teachers put a 1 in the row as the value if the child knows that letter/sound or a 0 if they don’t This way we know which letters/sounds most children know.

What I want to be able to do is compile the data so that for each letter/sound I can get a list underneath of the children so don’t know it yet, so I know who to practice what with.

So my question is - what function would help me do this and is there a good place I can learn how!

Thank you 😊

Posted Jul 17, 2020, 12:46 PM UTC

314
cpt_lanthanide56•

Edit: I updated the file linked because the file I made first only listed students who had learned the words (1s). Modified it and the screenshots to make it such that you can choose to filter for either the 0s or the 1s as you wish from a drop-down in the summary sheet.

Hi, I made this file quickly for you, teachers deserve all the help they can get.

It supports 500 names and 67 sounds, you can have a look and update it as you need. Change the data in the sheet "Input" for your use, and the "Summary" sheet shows you the lists.

Screenshots:
Summary Sheet (with a drop-down): https://i.imgur.com/kcO4KYE.jpg
Input Sheet: https://i.imgur.com/xrdBJk1.jpg

I tried to keep it basic, gauging your proficiency, so that you can replicate it. If you need to add more students than 500 it is simple, you just have to drag down the formulas in the Summary and Intermediate sheets.

If you need more sounds than 67, you need to add columns in all three sheets, and you'll have to drag the formulae to additional columns in the Summary, and Intermediate Sheets

3
Delicious-NumberOP•

Solution Verified thank you very much!

3
Clippy_Office_AsstMOD•

You have awarded 1 point to cpt_lanthanide

^I ^am ^a ^bot, ^please ^contact ^the ^mods ^with ^any ^questions.

1
Delicious-NumberOP•

You have actually made me cry thank you I am really touched!

I only have reddit on my phone and can’t get it at school due to firewall so I will have to wait till I get home to have a look 😊

2
cpt_lanthanide56•

Sure, drop me a pm if you'd prefer I email.

1
Albot029•

I'm not sure, but couldn't you just look at a column to see which child knows the letter? Maybe do some conditional formatting to make it clearer. You can also look into filters (inside the data tab)

2
Delicious-NumberOP•

Solution Verified thank you for taking time to help!

2
Clippy_Office_AsstMOD•

You have awarded 1 point to Albot02

^I ^am ^a ^bot, ^please ^contact ^the ^mods ^with ^any ^questions.

1
Delicious-NumberOP•

Thank you for answering

We can do that - I have the table conditionally formatted so the cell is green if they know and red if they don’t and I can do filters too but I would need to do it individually for each different sound I think and there are 67 sounds we have to teach.

We are a large infant school so it is quite time consuming and it needs compiling into simple lists as out teaching assistants sometimes get a bit overwhelmed by data presented in spreadsheets.

I was just trying to see if there was a way to make lists without continuing to do it manually!

1
Albot029•

Okay, I got an idea that will probably work, but it's by far not the most elegant way to do it. You look for a space that can fit a table as big as the original one. Then you write =IF(B2=1,$A2&", ","") in the top left cell of that space. Use this little autofill function to drag this formula across to cover the whole space. Now there should be some names floating around. The second step is to collect these names per column. That can be done with the formula =LEFT(CONCAT(B8:B13),LEN(CONCAT(B8:B13))-2). You could for example write it just below the table. Just drag this formula along the columns as well to get the list for each letter/sound

1
Delicious-NumberOP•

Wow thank you I will give it a go!

2
AutoModeratorMOD•

u/Delicious-Number - please read this comment in its entirety.

Once your problem is solved, please reply to the answer(s) saying Solution Verified to close the thread.

Please ensure you have read the rules -- particularly 1 and 2 -- in order to ensure your post is not removed.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1
[deleted]•

[deleted]

1
[deleted]•

[deleted]

1
Delicious-NumberOP•

Solution Verified thank you for your help!

1
AutoModeratorMOD•

Hello!

It looks like you tried to award a ClippyPoint, but you need to reply to a particular user's comment to do so, rather than making a new top-level comment.

Please reply directly to any helpful users and Clippy, our bot will take it from there. If your intention was not to award a ClippyPoint and simply mark the post as solved, then you may do that by clicking Set Flair. Thank you!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1