Software

Ask something
Discussion Locked

Filtering Out Blanks in a Table

0 helpful
12 replies

by cccgsmith

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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?

12 replies

by chitosunday

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.

by chitosunday

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=""

by cccgsmith

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?

by dklippert

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.

by cccgsmith

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.

by twobordercollies

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.

by cccgsmith

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.

by cccgsmith

I noticed that this had never been closed. I got some good ideas from the postings and have gotten my task accomplished. Thanks to all.

This discussion is closed to new replies.