EXCEL - Match Name, Sort, Check if "Active"

Hello All,

I recently posted a question to how match and sort a list of names to positions. However, I forgot that they needed a third variable which is only list those that are active. (Thank you RobertMika for this)

Please see spreadsheet attached that has the formula I wanted to list the names in order; but doesn't filter out just the actives.

Can someone please update the formula for me to only show active people...

Thanks in advance, it's much appreciated.

_Jason

Originally Posted by Richard Buttrey

Have you considered a pragmatic solution and sorting the Employee status data using the Active column as the key, then changing the Index range in the formulae to use row 86 instead of 105.

That has the advantage of not overcomplicating the formula.

Hi Richard,

That would work if it weren't for the fact this is just a small portion of a much larger spreadsheet. The Active and Departed is constantly changing and in reality I would be scan the whole column with a range $c:$c.

Originally Posted by Richard Buttrey

I don't see why that should rule this out as a technique. It could easily be encapsulated in a macro which you could run automatically at the press of a button

Hi Richard,

Yes, I wouldn't rule it out as a technique for others. But formyself I would want to just include it in the formula I already have in my example calculating the match name and sorting it inside the cell (provided by RobertMika)

=IFERROR(INDEX('Data-EmployeeStatus'!$A$2:$A$105,SMALL(IF('Data-EmployeeStatus'!$B$2:$B$105=Results!A$1,ROW('Data-EmployeeStatus'!$B$2:$B$105)-ROW($B$2)+1),ROWS($A$2:A2))),"")


I just need to add in the other portion of "IF('Data-EmployeeStatus'!$C$1:$C$12000="Active" that I read and saw somewhere else that works. But i'm able to get it to work as I don't know where in the formula to put it and how to close off the if false portion.

Although I agree your technique is viable I rather have the formula that calculate automatically as information is updated then a macro that I would have to run separately to the formula that's already on my sheet.

Thanks,

anyone able to help?

Please see the file, hope it works

Both you guys, Bebo021999 and Azumi, have it perfect.

Thanks!!!!