1. help with excel filtering

Hi All,

I use an excel spread sheet with a column that contain a series of numbers (2 to 2400), where more than one number appears in the column they are separated with a ‘~’ please see example below

I need to regularly filter this column and can successful achieve this by going to Filter>Text Filter>Contains> ~~105~~ or whatever number I require

This will return me all the records with the number 105

However, often I will need to search for a lot of numbers, in which case I perform a seperate filter search for each number required

Column example below (it may be I need 105, 244, 6, 210, 255,

~105~244~245~247~298~6~
~105~244~298~6~
~105~245~247~251~352~355~12~
~105~245~6~
~105~246~12~
~105~298~355~6~
~105~298~6~
~105~6~
~210~105~6~
~210~246~13~
~210~247~6~
~210~251~13~
~210~251~55~13~
~210~255~105~298~6~
~210~255~105~44~6~

Is it possible to enter a string of number and return all the relevant records on one hit?

Many thanks

2. If I assume your header is in A1 and the sheet is A2: A2400. In D1: D5 enter the values: 105, 244, 6, 210, and 255 [which value goes in which cell is immaterial]. Then in B2 enter the array formula (confirm with ctrl-shift-enter):
=OR(ISNUMBER(SEARCH("~"&\$D\$1:\$D\$5&"~",A2)))

Copy B2 and paste it to B3:B2400. Then filter on Column B for True for those which contain any of the 5 records.

Steve
PS if you want to filter on those that contain all 5 of the items (not just any one of the 5) then change the formula (again, confirm with ctrl-shift-enter) to:
=AND(ISNUMBER(SEARCH("~"&\$D\$1:\$D\$5&"~",A2)))

3. The Following User Says Thank You to sdckapr For This Useful Post:

Happy Larry (2013-07-03)

4. Originally Posted by sdckapr
PS if you want to filter on those that contain all 5 of the items (not just any one of the 5) then change the formula (again, confirm with ctrl-shift-enter) to:
=AND(ISNUMBER(SEARCH("~"&\$D\$1:\$D\$5&"~",A2)))
Steve,

I don't see how this would work since it doesn't account for the intermediate ~'s. Am I missing something? Does it have to do with the fact that it is an array formula?

5. Hi RG

Steve's formulas work for me.
It's because it's an array formula.

To see this, put your cellpointer in cell [B2], and in the formula bar, carefully highlight the range "~"&\$D\$1:\$D\$5&"~"
With this part of the formula highlighted, press [F9] to see the partial calculation in the formula bar.
You should see {"~105~";"~244~";"~6~";"~210~";"~255~"}

So, you are searching the adjacent cell for a match for any of these numbers.

zeddy

6. Thanks for all your help!

Nearly there.....but I find that it returns a True for any entry that contains any of the numbers, for example i'm searching for 6, it will return the records with 6 and 26, 36 etc

7. Total success!

by presending the D number to be searched for as ~~6~~ next number ~~105~~ etc it then only returns the records with the entire number.

Marvellous! Thanks for all you help

8. Using VBA

Happy,

Here is a different approach using VBA. Enter the numbers you wish to search into D1 separated by a "~" (without the quotes) then press the "Run Code" button. The matching numbers found will turn red. Spaces after the "~" will be ignored. Clicking the "Clear Search" button, will return the font colors back to black, equivalent to selecting the cells and manually clicking black font color on Home menu. Paste into a standard module. Add buttons to launch code or use Macros button on Developer menu.

HTH,
Maud

searchnumbers1.png

Code:
```Public Sub Findnum()
'DECLARE VARIABLES
Dim key As Variant  'SEARCH NUMBERS
Dim str As Variant  'STRING NUMBERS
Dim I As Integer, J As Integer, K As Integer  'COUNTERS

'RETURN STRINGS TO DEFAULT COLOR- BLACK
On Error Resume Next
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row  'LAST ROW
Range(Cells(2, 2), Cells(LastRow, 1)).Font.Color = 1 'STRINGS TO BLACK

'FIND MATCHES
key = Split(Cells(1, 4).Value, "~")  'SPLIT SEARCH NUMBERS
For I = LBound(key) To UBound(key)  'CYCLE THE NUMBER OF SEARCH NUMBERS
For J = 2 To LastRow  'CYCLE THE NUMBER OF ROWS
Cells(J, 1).Select
str = Split(Cells(J, 1).Value, "~")  'SPLIT THE STRING NUMBERS
For K = LBound(str) To UBound(str) 'CYCLE NUMBER IF STRING NUMBERS
If Trim(key(I)) = Trim(str(K)) Then  'IF MATCH FOUND....
'CHANGE THE FONT COLOR OF THE MATCHED NUMBER
Start = InStr(1, Cells(J, 1).Value, Trim(key(I)))
With ActiveCell.Characters(Start, Len(str(K))).Font
.Color = -16776961
End With
End If
Next K
str = Nothing  'CLEARS ARRAY VARIABLE
Next J
Next I
key = Nothing  'CLEARS ARRAY VARIABLE
Application.ScreenUpdating = True
End Sub```

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•