Array formula lookup

Hi,

I am new to array formulas, but so far I am thoroughly impressed with what they can do. I am stuck right now on one and was hoping for some 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 would generate for all the students absent on that date. The formula would need to search row 1 for the date, find all "A"s in the column of that date, and return the name in column A. Can this be done?

Thanks for any replies.

CJ-22

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

"CJ-22" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I am new to array formulas, but so far I am thoroughly impressed with
> what they can do. I am stuck right now on one and was hoping for some
> 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 would
> generate for all the students absent on that date. The formula would
> need to search row 1 for the date, find all "A"s in the column of that
> date, and return the name in column A. Can this be done?
>
> Thanks for any replies.
>
> CJ-22
>
>
> --
> CJ-22
> ------------------------------------------------------------------------
> CJ-22's Profile:
> http://www.excelforum.com/member.php...o&userid=17551
> View this thread: http://www.excelforum.com/showthread...hreadid=506632
>

Typo correction:

> Formula is: =(ISERROR(cell_ref)

Should be:

Formula is: =ISERROR(cell_ref)

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "CJ-22" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I am new to array formulas, but so far I am thoroughly impressed with
>> what they can do. I am stuck right now on one and was hoping for some
>> 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 would
>> generate for all the students absent on that date. The formula would
>> need to search row 1 for the date, find all "A"s in the column of that
>> date, and return the name in column A. Can this be done?
>>
>> Thanks for any replies.
>>
>> CJ-22
>>
>>
>> --
>> CJ-22
>> ------------------------------------------------------------------------
>> CJ-22's Profile:
>> http://www.excelforum.com/member.php...o&userid=17551
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=506632
>>
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I am new to array formulas, but so far I am thoroughly impressed with
> what they can do. I am stuck right now on one and was hoping for some
> 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 would
> generate for all the students absent on that date. The formula would
> need to search row 1 for the date, find all "A"s in the column of that
> date, and return the name in column A. Can this be done?
>
> Thanks for any replies.
>
> CJ-22
>
>
> --
> CJ-22
> ------------------------------------------------------------------------
> CJ-22's Profile:
http://www.excelforum.com/member.php...o&userid=17551
> View this thread: http://www.excelforum.com/showthread...hreadid=506632
>

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.

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?

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.