Solved: Find records and return values to new column

[ironj32]

Here's my situation: I have one Column (Col A) which has a list of values. In Col B I would like to filter out the values in Col A based on some criteria.

For example, below I would like Col B to show all of the records in Col A where the LEFT six characters = "Active".

Col A:
Inactive_Jim
Active_John
Active_Frank
Inactive_Betty
Inactive_Lucy
Active_Tina
Inactive_Jill
Active_Tom

Col B would result in:
Col B:
Active_John
Active_Frank
Active_Tina
Active_Tom

Any help is greatly appreciated.

[Bob Phillips]

Put this in B1 and copy down

=IF(ISERROR(SMALL(IF(LEFT($A$1:$A$8,6)="Active",ROW($A$1:$A$8),""),ROW(A5))),"",
INDEX($A$1:$A$8,SMALL(IF(LEFT($A$1:$A$8,6)="Active",ROW($A$1:$A$8),""),ROW(A5))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.

[ironj32]

thanks XLD. i was hoping to see if this can be accomplished using VBA. i was actually already using this, which works fine:

=INDEX('FEED-KRI'!$A$1:$A$300, SMALL(IF(LEFT('FEED-KRI'!$A$1:$A$300,24)="US Bank Active Employees",ROW('FEED-KRI'!$A$1:$A$300),""), ROWS('FEED-KRI'!$A$1:$A1)))

But I wanted to use the same formula in the next column over, except I switched
(LEFT('FEED-KRI'!$A$1:$A$300,24)="US Bank Active Employees" to (LEFT('FEED-KRI'!$A$1:$A$300,28)="US Bank Terminated Employees"

I would not let me use this in a separate column, which is why I was thinking that VBA might be the better way to go?

[Bob Phillips]

I would suggest adding a heading bin row 1 and put the value to compare againt in B1, C1 etc., and use this which can be copied down and across

=IF(ISERROR(SMALL(IF(LEFT($A$2:$A$9,LEN(B$1))=B$1,ROW($A$2:$A$9)-ROW($A$2)+1,""),ROW(A1))),"",
INDEX($A$2:$A$9,SMALL(IF(LEFT($A$2:$A$9,LEN(B$1))=B$1,ROW($A$2:$A$9)-ROW($A$2)+1,""),ROW(A1))))

VBA is for wimps!

[Bob Phillips]

BTW, you didn't change both instances of the text in your formula.

[ironj32]

Works perfectly, thanks XLD!