Append data from a column to separate table array

[miss_q]

I have a little problem.

Cells A1 - A10 contain student names.
Cells B1 - B10 say either 'absent' or 'present'.

I need to send letters to the students who were absent.

Therefore I would like those students who were absent to be extracted
from the names in A1 - A10 and put in another table array somewhere on
the sheet i.e. if Column B value is 'absent' then append the value in
column A.

Can this be done? Or have I not explained myself properly?

 

[Dave Unger]

Hi miss_q

If I'm understanding your question correctly, try a formula like this
in column C:

=IF(B1="absent",A1,"")

Hope this helps,

Dave U

 

[miss_q]

Hi Dave,

Thanks for replying.

Rather than the names appearing in an adjacent column an din
corresponding rows, I wanted the names to appear, one under the other,
in a separate table array. (See attached.)

Any guesses?

TIA


+-------------------------------------------------------------------+
|Filename: student letters.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4112 |
+-------------------------------------------------------------------+

 

[Ron Coderre]

I think an Advanced Filter will suit your needs:

Using your example with data in Cells A1:B14...

D1: Attendance
D2: Absent

A16: Student

Select A1:B14
Data>Filter>Advanced Filter
Select: Copy to Another Location
List Range: (your already selected list)
Criteria Range: Select D12
Copy To: Select A16
Click the [OK] button

That will create a list of names, under A16, of Students with an
Attendance value of Absent.

Does that help?

Regards,
Ron

 

[miss_q]

thanks for replying.

Rather than the names appearing in an adjacent column, and in
corresponding rows to the absentees (which would mean blank rows in
between), how can I get the names to appear, one under the other, in
another table array?

TIA


+-------------------------------------------------------------------+
|Filename: student letters.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4115 |
+-------------------------------------------------------------------+

 

[miss_q]

Ooops!

My computer is being weird, it was showing me that my first reply
didn't go through...


But yes, it worked!


Thanks a bunch!