Quick Fix for Indexing?

[cparks]

Im a manager at a grocery store and I'm in charge of stocking aisles.

In this instance, I would like to achieve 2 things:

1. Condense a list of employees that are on-duty (get rid of blank cells) so that they can be included into my second table where...
2. Based on a randomly selected aisle assignment (I already have the formula for), assign that random aisle to an on-duty employee.

The columns I have highlighted are what my end result will look like in the appropriate tables.

Any more questions, please do not hesitate.

Thanks much.

CP

 

[cparks]

***UPDATE***

I have FINALLY figured out my #2 issue.
=IFERROR(INDEX($J$3:$J$32,MATCH(A3,$I$3:$I$32,0)),"")

Now if I can get some help on my #1 issue, that would be great guys! I know there's ways in Excel to manually condense blank cells through the ribbon, but I would like to see if theres a way through a formula.

 

[bosco_yip]

#1 issue,

I3, array formula (confirm enter with CTRL+SHIFT+ENTER) copy down :

=INDEX(A$3:A$32,SMALL(IF(B$3:B$32="PRESENT",ROW(C$3:C$32)-ROW(C$3)+1),ROWS($1:1)))

Regards

 

[cparks]

#1 issue,

I3, array formula (confirm enter with CTRL+SHIFT+ENTER) copy down :

=INDEX(A$3:A$32,SMALL(IF(B$3:B$32="PRESENT",ROW(C$3:C$32)-ROW(C$3)+1),ROWS($1:1)))

Regards

Great bosco, thanks!!!