Results 1 to 7 of 7
Thread: help with excel filtering

20130702, 11:11 #1
 Join Date
 Dec 2009
 Posts
 34
 Thanks
 3
 Thanked 0 Times in 0 Posts
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

20130702, 12:20 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 ctrlshiftenter):
=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 ctrlshiftenter) to:
=AND(ISNUMBER(SEARCH("~"&$D$1:$D$5&"~",A2)))Last edited by sdckapr; 20130702 at 12:41.

The Following User Says Thank You to sdckapr For This Useful Post:
Happy Larry (20130703)

20130702, 14:18 #3
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,491
 Thanks
 377
 Thanked 1,473 Times in 1,340 Posts
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20130702, 17:42 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,953
 Thanks
 140
 Thanked 521 Times in 497 Posts
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

20130703, 08:08 #5
 Join Date
 Dec 2009
 Posts
 34
 Thanks
 3
 Thanked 0 Times in 0 Posts
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

20130703, 08:25 #6
 Join Date
 Dec 2009
 Posts
 34
 Thanks
 3
 Thanked 0 Times in 0 Posts
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

20130703, 11:29 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,689
 Thanks
 122
 Thanked 668 Times in 609 Posts
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
Last edited by Maudibe; 20130711 at 22:44. Reason: color the comments