CODEWITHSUNDEEP
excel
keynesiancross
keynesiancross

Reputation: 3529

excel - filter non-static list on first three letters

I'm trying to filter out some values from a non-static list, and have the results sit in another range. So:

OriginalList:
ABC_1
ABC_2
z_1
z_2

I want to filter out anything that starts with ABC, into another list so I end up with:

NewList:
z_1
z_2

The original list can move / get mixed up. And I can't use VBA, need this to automatically update. And I can't have blank cells in between results in NewList.

Thanks -KC

Upvotes: 0

Views: 67

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

With data in column A, in B1 enter:

=IF(LEFT(A1,3)="ABC","",1)

and in B2:

=IF(LEFT(A2,3)="ABC","",1+MAX($B$1:B1))

and then copy B2 downwards.

Then in another cell, say D1 enter:

=IFERROR(INDEX(A:A,MATCH(ROW(),B:B,0)),"")

and copy downward:

enter image description here

Using B as a "helper" column allows us to avoid array formulas.

Upvotes: 1

Related Questions