Append data from a column to separate table array

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?

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

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

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 D1:D2
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

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

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!