I've got a huge 2x2000 column of data and I want to create a formula in another column such that that new column will only include those rows from the original column that match a pattern.

Example:

Original Column:
aaa 2
aab 3
aac 1
aad 9
bbc 4
dd2 5
aat 9


Generated Column based on pattern "aa":
aaa 2
aab 3
aac 1
aad 9
aat 9

[Edited on March 15, 2006 at 9:30 AM. Reason : s]

3/15/2006 9:30:27 AM

You can extract the rows you want into a new column, then sort the new columns to exclude the blank spaces.

e.g. assuming your original data is in A1:B2000
In column C, use the formula
=IF(LEFT(A1,2)="aa",A1,"")
then in column D use the formula
=IF(C1="","",B1)

Then fill down for all 2000 rows. All the rows matching your pattern will show up, and all the others will be blank cells. Then you'd need to copy columns C and D and "paste as values" in the same place (to get rid of the formula), then do a sort on C and D and it will get rid of all the blank cells.

Obviously this is a one-time solution - not dynamic. If you want it to be dynamic, probably need some VBA.

3/15/2006 9:48:49 AM

how close is your example to what you're really trying to do?

[Edited on March 15, 2006 at 9:50 AM. Reason : ]

3/15/2006 9:49:37 AM

^ my example matches exactly what i am trying to do. The only difference is that I will probably want to use slightly more complicated pattern matching.

3/15/2006 9:55:47 AM