General discussion

Locked

Filtering Out Blanks in a Table

By cccgsmith ·
I keep an Excel table of students, their exam scores and when they took the exam. There is a row for each student, with the columns being the various exam grades and the dates they took the exam. I wish to create a list somewhere in the worksheet or workbook of those students that have not yet taken the exam. This list would key upon missing scores in the column. I envsion the created table being similar to what I have, but with a list of the names of those students with blank scores underneath each grade column. I do not want blanks in the list. And I want to be able to print this list out. Plus I would like for the extracted date to update whenever a missing grade is entered. How do I go about accomplishing this?

This conversation is currently closed to new comments.

12 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by chitosunday In reply to Filtering Out Blanks in a ...

Click data menu, filter, advance filter, action filter the list in place, list range put the range of the data source, criteria range just range two blank rows then in the last row put the formula =d4="" (replace the d4 to the location where the actual first score is located.

Collapse -

by chitosunday In reply to

the last row i was referring is the last row of where you put your criteria range

Collapse -

by chitosunday In reply to

you don't need a different formula for each, just my formula (one column and two rows that is one top blank and the last row with the formula =your first score range=""

Collapse -

by cccgsmith In reply to

Poster rated this answer.

Collapse -

by cccgsmith In reply to Filtering Out Blanks in a ...

I have been trying to use this approach to no avail. Probably I am trying to "bite off more than I can chew"! I want to have a separate list of names under each column of grades. Assuming the names are in column A, and the grades are in columns B, C, ... Do I need a different formula in the Critera range for each grade column?

Collapse -

by DKlippert In reply to Filtering Out Blanks in a ...

Choose one cell in the table and then go to:
Data>Filter AutoFilter
Choose one of the items in the header row and drop down the filter list.
At the bottom of the list you can choose Blanks.

Collapse -

by cccgsmith In reply to

I have been using AutoFilter, but it does not give me the dynamic update for the extracted data. I need a printout of all exams and all people having missing grades. I have done this manually by using Blanks, and then copying and pasting the results in an area below the column, but this is tedious.

Collapse -

by twobordercollies In reply to Filtering Out Blanks in a ...

This answer will sound familiar but hear me out....

Rather than having two lists keep all the data in one list and use your autofilter. Autofilter for blanks and while you have this filter applied print it and it will print only the records of people who have blanks for their test scores.

This way there is no need to update a list or maintain two seperate lists with the threat of making a mistake.

Collapse -

by cccgsmith In reply to

You are definitely correct regarding the maintenance of two lists--that is why I want a better method. However, using AutoFilter only enables me to analyze one exam at a time and I wish to analyze all of them at the same time. I am quite certain that the Advanced Filter is the way to go, except that I cannot make it work and still need help. I have not been able to make it work with blanks on one exam. Once I accomplish that, I believe that I can expand upon the method and accomplish my desired goal.

Collapse -

by chitosunday In reply to Filtering Out Blanks in a ...

You can email your file to monsunday1959@yahoo.com
to give you example of advance filter

Back to Software Forum
12 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums