Results 1 to 7 of 7
  1. #1
    Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 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)))
    Last edited by sdckapr; 2013-07-02 at 12:41.

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

    Happy Larry (2013-07-03)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by sdckapr View Post
    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?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 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

  6. #5
    Lounger
    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

  7. #6
    Lounger
    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

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 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
    Attached Files Attached Files
    Last edited by Maudibe; 2013-07-11 at 22:44. Reason: color the comments

Posting Permissions

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