Solved

Filter & show different values from any record

Posted on 2011-03-22
16
180 Views
Last Modified: 2012-05-11
Dear experts,

I'm trying to figure a way how to include records that are ''different''.

A sample is included below.

A new button was added, filter by rccd & acctopid.
After a search, some records need to be shown. If I enter 13, it will show only those that belong to this number, but how to make it show the ones that also belong to this number, but that are like 13 C ?

Thanks
currentdb
userform-sample-v7b-6.xlsm
0
Comment
Question by:currentdb
  • 8
  • 8
16 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35192876
Looking into it :)

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35192892
Good :)
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35192942
Try this

Private Sub CommandButton1_Click()
    Dim lastRow As Long
    
    On Error GoTo Whoa
    
    lastRow = Sheets("QUERY_FOR_GSL2").Range("A" & Rows.Count).End(xlUp).Row
    
    If Len(Trim(TextBox1.Text)) = 0 Or Len(Trim(TextBox2.Text)) = 0 Then
        MsgBox "The textbox cannot be empty"
        Exit Sub
    End If
    
    With Sheets("QUERY_FOR_GSL2").Range("$A$1:$BC$" & lastRow)
        .AutoFilter Field:=1, Criteria1:=TextBox1.Text
        .AutoFilter Field:=15, Criteria1:="=" & TextBox2.Text, _
        Operator:=xlOr, Criteria2:="=" & TextBox2.Text & "*"
        Unload Me
    End With
    Sheets("QUERY_FOR_GSL2").Activate
    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Open in new window


Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35193009
Wow! That was fast! :)

And it works as expected. Just made another test. If inserting the value 13 C, would that return both 13 and 13 C values ?

If I insert only 13, it will return 13 and 13 C, but if doing the inverse thing, it will only return 13 C. How I can make it return 13 C and 13 ?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193040
AFAIK, the reverse is not possible :( I could be wrong though.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35193088
If the reverse is not possible, then we will leave it as it is. I just checked and there are 5 spaces...13 space 5 times then C. LOL

I will add a note as it's better to insert 12 or 13 for that matter and it will show relevant records plus the ones with the ''C''. It's just easier as it is.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193095
>>>I will add a note as it's better to insert 12 or 13 for that matter and it will show relevant records plus the ones with the ''C''. It's just easier as it is.

That makes sense :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193102
Wait... I think there is an alternative. Let me give it a shot.

Sid
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Closing Comment

by:currentdb
ID: 35193112
Thanks again for your fast help, the fastest one amongst all ee experts! :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193157
Seems you missed my post ID: 35193102 ;)

Try this :)

Private Sub CommandButton1_Click()
    Dim lastRow As Long
    Dim findText As String, MyArray() As String
    
    On Error GoTo Whoa
    
    lastRow = Sheets("QUERY_FOR_GSL2").Range("A" & Rows.Count).End(xlUp).Row
    
    If Len(Trim(TextBox1.Text)) = 0 Or Len(Trim(TextBox2.Text)) = 0 Then
        MsgBox "The textbox cannot be empty"
        Exit Sub
    End If
    
    With Sheets("QUERY_FOR_GSL2").Range("$A$1:$BC$" & lastRow)
        .AutoFilter Field:=1, Criteria1:=TextBox1.Text
        
        If InStr(1, TextBox2.Text, " ") Then
            MyArray = Split(TextBox2.Text, " ")
            findText = Trim(MyArray(0))
            .AutoFilter Field:=15, Criteria1:="=" & TextBox2.Text, _
            Operator:=xlOr, Criteria2:="=" & findText
        Else
            .AutoFilter Field:=15, Criteria1:="=" & TextBox2.Text, _
            Operator:=xlOr, Criteria2:="=" & TextBox2.Text & "*"
        End If
        Unload Me
    End With
    Sheets("QUERY_FOR_GSL2").Activate
    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:currentdb
ID: 35193161
Sure no problem. I closed the question too fast, but I still receive notifications from my knowledge base. So you can try a shot while you have time of course :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193172
>>>>Sure no problem. I closed the question too fast, but I still receive notifications from my knowledge base. So you can try a shot while you have time of course :)

Already done that ;)

See above.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35193173
Ok lemme give it a try now :)
0
 
LVL 1

Author Comment

by:currentdb
ID: 35193219
Looks like it works, so the reverse thing was not impossible after all :)

Thanks you again for your time and a very great help through this.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35193235
No it is not impossible if you use a workaround like I did :)

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35193251
Of course you are right and it was a very good workaround after all :)

Thanks again :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now