How to Filter and Sort Student Data

[jhellwich]

I'm a principal of an elementary school and we're trying to use excel data to help us with interventions for students not learning. I've attached a spreadsheet.

What I'm trying to do but don't have enough excel experience is to figure out how to capture in a cell all of the names on the left that have not met standard in a certain area.

Not meeting standard is defined as a zero (red), 1 or 2 (yellow). Thanks in advance for any help. Thanks.

I removed last names.

[Sofistikat]

Hi jhellwich

I have a few questions for you:

The first thing I noticed from the spreadsheet you attached is that the conditional formatting you've used varies from column to column, so there does not appear to be a consistent rule you're using to identify a students' level of achievement. For instance, a score of 2 gets a yellow background in Column F, whereas the same score has a green background in Column G. Could you please clarify the reason for this?

Also, I presume that you want to collate a list of students for all areas in which they underachieve, or are the only certain areas that you want to highlight?

And lastly, do you want to capture all students not meeting standard in just one single cell?

[MarvinP]

Hi jhellwich,

I believe you want to create a table of your data. Then filter only showing 0 and 1 for the first column and sort by that number. This will give you a list of names you can copy and paste to another region. Do this with each skll and you will have it. Putting all the name in a single cell defeats the purpose of having it in Excel.

Also - please click on Edit on the first message and change the Title name of this thread to How to Filter and Sort Student Data". Your title as is will get a reprimand from the forum moderators.

[peterjuhnke]

Hello
I assume if they get a zero in any one of the columns then it is a fail.
Please have a look.
I added extra stuff in col C which you will probably want to remove.
If you want them to be gathered together, then a DATA SORT will do that, or you can use the filter.
Regards
Peter

[jhellwich]

Thank you for your help and questions. Not all of the questions are worth the same value, which is the reason for the difference. What it boils down to is I want a list of kids who are yellow or red for each question. I wrote that those names would appear in one cell; I don't really have a preference how it appears, I just would like to save the steps of having to sort that by hand. Thanks in advance for any assistance you might be able to give me.

[Charlie_Howell]

HI wasn't clear on what you wanted to do with the yellow 2's and 3's but here is a start

[Sofistikat]

Hello again Jhellwich

Attached is a file with a whopping big "Process" button which runs a macro to list students based on criteria specified in hidden columns.

I hope this helps.

[jhellwich]

Thank you Charlie and Sofistikat. I love the Process macro. I'd like to tweek it a bit for this sheet (it's kicking out the 0's and 1's on 4 point items, but I'd like 2's also - all the yellows and reds - because they aren't at standard yet and need interventions. Also if it wouldn't take me a college course to figure out I'd love to be able to tweek it for other tests (all with 2 or 4 point items only). Thanks.

[Sofistikat]

I think you'll get a better result by fine-tuning the criteria in columns K:Q which I hid in the example I sent to you. I've just made a few alterations to my copy and it is now picking up students that it wasn't previously. Let me know if I can be of further assistance.

[jhellwich]

Sofistikat - thank you, this is exactly what I was looking for- thank you! I'm not sure if this question makes sense, but I went in and changed the criteria...how do I "reset" the macro so when I hit Process it creates the list?

[Sofistikat]

I've included a Reset button in the attached version, which will delete the lists it creates. Happy to be of service :-)

[jhellwich]

Perfect! Thank you!