Remove Certain Information From a List and Auto Create New List On Other Sheet

[unpluggedmusic]

Dear all,

I have a difficult task to do at work and I need expert help over here again.

I have made a sample excel file that contains a normal list for an 'Employee database'.

I would like to be able to automatically port over the information for employees that are still 'Present' and for those that are 'Resigned', I do not want them to appear in the list. And I need the blanks to be removed and the list is being shifted up whenever there is a blank.

Is that possible with a formula?

Please see my attached.

Thank you all for your help.

[Fotis1991]

In A2 put this ARRAY(confirm using control+shift+enter-not just enter) formula.

=IFERROR(INDEX(DataBase!A$2:A$34,SMALL(IF(DataBase!$E$2:$E$34="Present",ROW(DataBase!A$2:A$34)-1),ROW(DataBase!A1))),"")

Copy down and across.

[Ghozi Alkatiri]

we need 2 formula
first at A2 to get new serial number
=IF(ROW(A1)>COUNTIF(DataBase!$E$2:$E$34;"present");"";ROW(A1))
copy down

the second formula (formula array) at B2
=IF($A2="";"";INDEX(DataBase!B$2:B$34;SMALL(IF(DataBase!$E$2:$E$34="present";MATCH(DataBase!$B$2:$B$34;DataBase!$B$2:$B$34;0));$A2)))

copy right and down

[unpluggedmusic]

Oh My God... you guys are great! that was supereasy to copy and paste! Thanks a million!!!