I'd like to make a list of names in google sheets

Question

We have a Church death insurance and of course we list down the names. If a member signs up, there's an "add" in the name. If said member opts out, we add a row below, paste his name and replace "add" with "drop". So there's a duplicate but technically not since the one has an "add" and the other has a "drop". If said member dies, same thing: add a row, paste his name then we put [deceased].

The format of the information inside the cell is Last name, First name status name of church.

ABA, ADONIS  Add Upper Sumilop Church
ANG, NICK  Add Upper Sumilop Church
ANG, NICK  Drop Upper Sumilop Church
CAW, DERNA Add Vetarba/Talagutong Church
CAW, DERNA Deceased Vetarba/Talagutong Church

I'd like to make one list of both 'active' (didn't drop) and 'alive' (didn't die) members. Based on the example above, I'd like to make a list where I can show Aba, Adonis.

I was able to make a list where on one column, there's the dropped members, and on the other the members who have died. these columns are adjacent to each other but non-adjacent to the column of the original list.

I wasn't part of the program from the start up until they've already established the current system I'm working on now.

*they're enclosed in brackets instead of being in quotation marks.

Answer 1

query it:

=QUERY({A:A}; "where not lower(Col1) contains 'deceased'"; )

=QUERY({A:A}; 
 "where not lower(Col1) contains 'deceased'
    and not lower(Col1) contains 'drop'"; )

or:

=QUERY({A:A}; "where lower(Col1) contains 'add'"; )