Array formula lookup

[CJ-22]

Hi,

I am new to array formulas, but so far I am thoroughly impressed wit
what they can do. I am stuck right now on one and was hoping for som
help. I am a teacher and I keep daily records of my students on excel
My daily record spreadsheet is similar to this:

Row 1 - Dates for each school day
Column A - Student Names
B2:CA50 - Daily Record keeping such as absence, tardy, homework, etc.

What I want is a column that will list all absences for a specific day
In other words, have a cell where I can input any date and a list woul
generate for all the students absent on that date. The formula woul
need to search row 1 for the date, find all "A"s in the column of tha
date, and return the name in column A. Can this be done?

Thanks for any replies.

CJ-2

 

[Biff]

Hi!

Here's one way:

A51 = date criteria

Array entered:

=INDEX(A$2:A$50,SMALL(IF(INDEX(B$2:CA$50,,MATCH(A$51,B$1:CA$1,0))="a",ROW(A$2:A$50)-ROW(A$2)+1),ROWS($1:1)))

Copy down until you get #NUM! errors meaning the data has been exhausted.

We can suppress the display of the #NUM! errors using an error trap in the
formula. This would make the formula twice as long and add to its
complexity. An alternative method is to use conditional formatting to hide
the errors.

Select the cells that hold the formula
Goto Format>Conditional Formatting
Formula is: =(ISERROR(cell_ref)
Click the Format button
Set the font color to be the same as the background color
OK out

Biff

 

[Biff]

Typo correction:

Formula is: =(ISERROR(cell_ref)

Should be:

Formula is: =ISERROR(cell_ref)

Biff

 

[RagDyer]

In case you might be interested in also creating a list of "Tardy", say you
enter the date of interest in CB1, and in CB2 you enter your "A" for absent,
or "T" for tardy, or whatever.
Then enter this *array* formula in CB3, and copy down enough rows to insure
that all possible names are returned.
You'll get a #NUM! error when the formula runs out of names to find.

=INDEX($A$2:$A$50,SMALL(IF(($B$1:$CA$1=$CB$1)*($B$2:$CA$50=$CB$2),ROW($A$1:$
A$49)),ROWS($1:1)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[CJ-22]

Thanks for the responses. They have worked well for me. However, this
experience has prompted me to want to take it a step further. Is it
possible to generate a list of all students with a specific comment and
list them in the order that the comments were given?

For example:
Column 1 = names
Row 1 = dates
B2:CA74 = records.

I want to generate a list of all students who have the letter "m"
(missing assignments) in their row. The previous formulas worked great
for generating a list based upon a specific column (date), but I now
want a list to be continuous throughout the whole grading term
(B2:CA74). I want to list all students who have missing assignments
and I want them to be in order by dates. I hope I have explained this
clearly. Thank you for any replies.

 

[CJ-22]

Hey Ragdyer,

Your array formula works well, but I have another problem I want to
solve. In some cases, there might be more than one comment in a cell.
For example, there might be a "t" for tardy and an "m" for missing
assignments. The formula as is will find all the single comments, but
if there is a cell that has multiple comments "tm" (tardy/missing
assignments) it only returns an error. Can this formula be modified to
find all the t's even if the cell contains a t and an m?

 

[CJ-22]

Thought I would add the formula I am currently using (thanks RagDyer and
Biff).

=INDEX($A$1:$A$426,SMALL(IF(($D$1:$BX$1=$CB$2)*($D$1:$BX$426=$CB$1),ROW(A$1:$A$425)),1))

CB2 = date to find from row 1
CB1 = letter to find from corresponding column

In each cell there might be more than one letter (ame), but I still
need to find the letter *a* if it is in that cell and return the name
from column 1. How can I make this formula find all a's even if there
is more than one letter in the cell?

Thanks for any replies.