filtering based on pattern

[Electriasta]

Hello!

I'm trying to filter a list of numbers based on pattern.

For instance if I want to have only the cells containing values that start with two sets of three identical numbers.

so that if my list contains two cells having "11100022" and "22233389" they will show up since both start with two sets of three identical numbers.

knowing this will allow me to filter using patterns


I would highly appreciate any advice!

thanks..

 

[rjwebgraphix]

This drove me a little batty today, but I got it figured out for you.

Everything you need is covered here...

Excel Advanced Filter -- Complex Criteria

...but I will explain in further detail so you don't have the same difficulty I did.

Advanced Filter will do the job for you. You can access advanced filter by clicking Data then Filter then Advanced Filter, but before you do that there are some other things that need to be done.

Here is the test data that I used.

Excel Workbook
	A	B
1	dummyHDR	Numbers
2	This	11100022
3	column	22233389
4	doesn't	65461000
5	matter	46432156
6	only	22246816
7	here	46546544
8	for	65461651
9	test	11198746
10	purposes	65465160
Sheet

As you can see, some rows have your 111 or 222 pattern.

Now you have to set up the criteria in which you want to filter. I suggest using cells that are outside the data range so that the filtered data will not conflict with the visibility of the criteria. In this case, with the data block ending at B10, I will start my criteria at D12. 2 cells right and 2 cells down from the data block, but in all actuality you can put the criteria anywhere.

This is how I set up the criteria in which Advanced Filter will use to filter the data...

Excel Workbook
	D
12
13	TRUE
14	FALSE
Sheet

Now just a bit ago I mentioned starting my criteria in cell D12, but as you can see D12 is Empty. This is what drove me crazy for a bit. You might think that your criteria starts in D13 because D12 is empty, although, D12 represents the header row. When filtering with a Formula, which is what this is, you must leave the header row blank, but the Header Row MUST be included in your criteria range or you will get no results of the filter.

Now lets look at the formulas in the criteria range...

Isnumber is simply going to produce a True or False. When using a formula as your criteria, the formulas can only produce a true or false. If they produce a value, it won't work.

Find is just finding whatever number pattern you are looking for and obviously whatever is in the quotes is the number you are looking for.

The cell reference, in this case is B2 for both formulas, should always point to the top most cell containing the data, but not the header row. That is why they are both B2.

Now that we have our criteria set, its time to run the advanced filter, however, before you do it, it would be easiest if you highlight your entire data block that you want to filter, but do not highlight what you set into cells for criteria.

With the data block highlighted
Click Data - Filter - Advanced Filter

You'll notice that List Range is already filled out for you. This is because you highlighted the data block first. You don't have to highlight first, but it can save time.

For now just leave it set to "Filter the list, in place"
Click in the Criteria Range box, now you can set your criteria range. In this case we want to set the criteria range to $D$12:$D$14. Remembering that even though D12 is empty, it still acts as the header row. Without setting the empty row too you will produce no results.

leave Unique Records unchecked.

Click ok and the results should look like this....

Excel Workbook
	A	B
1	dummyHDR	Numbers
2	This	11100022
3	column	22233389
6	only	22246816
9	test	11198746
Sheet

There you go. A step by step breakdown of filtering numbers by a pattern in the number.

However, if there are any letters in your pattern at all, then this is all moot as there's an easier way to do it if the cell contents contain text.

Hope this helps,

 

[Peter_SSs]

Electriasta, welcome to the MrExcel board!

.. I got it figured out for you.

Possibly, but I interpret the requirement differently ..

For instance if I want to have only the cells containing values that start with two sets of three identical numbers.

so that if my list contains two cells having "11100022" and "22233389" they will show up since both start with two sets of three identical numbers.

If my interpretation is correct, then for your sample data, rows 6 and 9 should not be visible since those values start with one set of three identical numbers only.

My suggestion also uses Advanced Filter. Note that I have changed rjwebgraphix's sample data a little.

My sample data, and Advanced Filter formula in D2 (D1 empty)

Excel Workbook
	A	B	C	D
1	dummyHDR	Numbers
2	This	11100022		TRUE
3	column	22233389
4	doesn't	65461000
5	matter	46432156
6	only	22222216
7	here	46546544
8	for	65461651
9	test	11199946
10	purposes	65465160
11
Advanced Filter

Apply AdvancedFilter to A1:B10 with Criteria range D1:D2.

Results of the filter:

Excel Workbook
	A	B
1	dummyHDR	Numbers
2	This	11100022
3	column	22233389
6	only	22222216
9	test	11199946
11
Advanced Filter

 

[Peter_SSs]

.. I got it figured out for you.

I forgot to mention one other thing about your suggestion:
It would show rows that contained "111" or "222" anywhere in the number, not just at the start.

 

[rjwebgraphix]

Possibly, but I interpret the requirement differently ..

=LEFT(B2,6)=REPT(LEFT(B2,1),3)&REPT(MID(B2,4,1),3)

You're probably right. I'm not entirely sure the pattern is clear, but having the basic concept of how to do it, which was my goal, the rest is merely having the correct formula criteria. It looks like each pattern needed could, in the end, have vastly different formula criteria.

Electriasta, if the criteria formula's don't work out for you as needed, feel free to followup with more detail how the pattern is laid out and the desired result.