• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter data based on a another sheet "Filterlist". Need a vlook up back to main sheet to show if value Does not exist.

J

Jonnathanjons

Member
Hello experts .
What I have : I have created a small project which I wanted to filter the data based on another sheet "Filterlist" as you see below. While running the script a new sheet name "Filtelist" is created and ask the user to enter the list of items in column A which will be filtered in the main sheet. Only 1 column to filter. The column to filter in the Main Sheet is based on user's selection in the header before executing the script. I have created this so far .It may not be very robust but running okay

What is required from you: I would like to have a column name in the sheetname "Filterlist" column "B" which should look up in the Mainsheet and tell me if any value in list does not exist . basically a vlook up with mainsheet and say this value that I provided does not exist in the column where user selected to filter in the Main Sheet. I also found that when I run my script many blank rows are included. Kindly help to ignore the blank cells in the column selected. attaching a worksheet as well as the script I have creatd so far
Result should look like this
83575
 

Attachments

  • Advance Filter v1.xlsx
    20.2 KB · Views: 1
Last edited:
According to the mandatory forum rules you must first edit your initial post and use the Code option from the 3 dots menu, thanks !​
​
As a reminder an Excel basics formula can here directly do the job rather than a VBA procedure …​
​
 
Code: 
Sub AdvncFilter()

             Dim tempCriteria As Variant
             Dim i As Long
             Dim Criteria() As String
             Dim rng As Range
             Dim e As Integer
             Dim FilterList As Worksheet
             Dim ShtName As String
             ShtName = "Filterlist"

          Set rng = ActiveSheet.UsedRange
                   If Not Evaluate("isref('" & ShtName & "'!A1)") Then
                   Sheets.add(After:=ActiveSheet).Name = "FilterList"
                   Msgbox ("Add your search list in Column A1 and proceed!!"), , "MESSAGE"
         Else
                   LastRow = Sheets("Filterlist").Cells(Rows.Count, 1).End(xlUp).Row
                   If IsEmpty(Worksheets("FilterList").Range("A" & LastRow)) Then
                   Msgbox ("Sheet 'FilterList is empty. Update 'FilterSheet' and proceed!!!"), , "MESSAGE" :  Exit Sub
         End If
        On Error GoTo Msgbox
                   e = Application.WorksheetFunction.Match(Selection, Range("1:1"), 0)
                   tempCriteria = Worksheets("FilterList").Range("A:A")   '<<<< Need help here to improve the search
                   ReDim Criteria(1 To UBound(tempCriteria))
                               For i = 1 To UBound(tempCriteria)
                               Criteria(i) = CStr(tempCriteria(i, 1))
                               Next
                              rng.AutoFilter e, Criteria1:=Criteria, Operator:=xlFilterValues
          Exit Sub
          Msgbox:
          Msgbox ("Choose 1 Filter Header"), , "< ERROR >"
     End If

End Sub
 
Thanks, to remind each time you post a code …​
​
So why not using a beginner level Excel formula ? (even under VBA)​
​
 
I new here .trying to learn your mandatory forum rules and I respect that..
Purpose: I use all these codes in the personal.xlsb file... and use command button to check multiple files when I research..
1) I will have around 500 values to filter from a 150 MB file every time I research from a new dump from db2
2) I need to also cross check out of 500 I provided how many of them have the returned the result from the db2 dump and how many of them are not found the result. Hence this look up back to the Main Sheet is important.

I hope I have justified the need for this macro.
 
Last edited by a moderator:
So attach at least a workbook with an already created FilterList worksheet in order any helper can give it a try​
or just post your working B2 cell formula …​
​
 
I am posting a macro enabled file with above scripts. But I am unable to create a lookup back to the Main Sheet as its stopping the entire above scripts to perform that I created Hence I had to remove it ..when the lookup is created its totally conflicting with filtering. Hence need your help.
 

Attachments

  • Advance Filter v2.xlsm
    27.9 KB · Views: 6
Assuming the Main Sheet is active - 'cause of Selection - and the FilterList worksheet already exists​
so according to beginner level Excel basics formula a starter VBA demonstration :​
Code: 
Sub Demo1()
    [FilterList!A1].CurrentRegion.Columns(2).Formula = "=IF(ISNUMBER(MATCH(A1," & Selection.Address(, , , True) & ",0)),""Ok"",""Not Exist"")"
End Sub
 
Marc L said:
Assuming the Main Sheet is active - 'cause of Selection - and the FilterList worksheet already exists​
so according to beginner level Excel basics formula a starter VBA demonstration :​
Code: 
Sub Demo1()
    [FilterList!A1].CurrentRegion.Columns(2).Formula = "=IF(ISNUMBER(MATCH(A1," & Selection.Address(, , , True) & ",0)),""Ok"",""Not Exist"")"
End Sub
Click to expand...
May I know where do I add this. Is it towards the end of my script to perform as the last action before End Sub
I tried to add this code towards the end but not see the desired result.
Can you please add it to the Module in the attached xlsm file
 
Code: 
Sub Macro1()
'
' Macro1 Macro recorded by the Noob Simulator
'

'
        Const M = "MESSAGE"
    With Sheets
        If .Count > 1 Then
           .Item(1).Activate
            If Selection.Column > ActiveSheet.UsedRange.Columns.Count Then Msgbox "Select a header !", 64, M _
                Else ActiveSheet.UsedRange.AutoFilter Selection.Column, Application.Transpose(.Item(2).UsedRange), 7
        Else
           .Add(, .Item(1)).Name = "FilterList"
            Msgbox "Add your search list in column A and proceed!!", 64, M
        End If
    End With
End Sub
 
Thank you Marc for reviewing the script .
I see an issue in the lookup it shows Not Exist for all the values in the filter list.
I guess I found the reason the script formula is currently pasting as below
=IF(ISNUMBER(MATCH(A2,'Main Sheet'!$G$1,0)),"Ok","Not Exist")
But its should be pasted as below to include the last row
=IF(ISNUMBER(MATCH(A1,'Main Sheet'!$G$1:$G$68,0)),"Ok","Not Exist")
 
Hello Marc, Sorry .. I think You missed a main point from my script . The main purpose is to :
1) filter in the Main Sheet on the Selected column header based on the values updated from the Sheet" FilterList"
2) Look up in the Sheet "FilterList" and column B1 with the Selected column and say Ok or Not Exist.

Currently your script is not performing the point 1.
 
Hello Marc Thanks for reviewing the script. Yes the look up part is working perfectly.
Kindly refer post#13 point 1. But can you please enrich the script to filter the Selected Column in the Main Sheet I tired to incorporate this scripts for the filter in the Main sheet after your last line before End Sub statement but its showing several errors. I put them after above codes you provided.
Can you pls correct what is wrong

>>> use code - tags <<<
Code: 
e = rng.Application.WorksheetFunction.Match(Selection, Range("1:1"), 0) '<<<< Selection column header name in the Main sheet

tempCriteria = Worksheets("FilterList").Range("A:A")

'tempCriteria = Worksheets("FilterList").Range("A1:A" & lastRow)

ReDim Criteria(1 To UBound(tempCriteria))

For i = 1 To UBound(tempCriteria)
    Criteria(i) = CStr(tempCriteria(i, 1))
Next
rng.AutoFilter Field:=e, Criteria1:=Criteria, Operator:=xlFilterValues
 
Last edited by a moderator:
As I have no clue of what you was trying to do, as your initial post asked for the 'Non Exist' so the reason why I very not need those codelines.​
​
As filtering according to some values existing in a column does very not require any VBA procedure as it's an Excel basics at kid level​
so trying to 'reinvent the wheel' is nothing but over complicating the every day user work with the risk to be 'squared' !​
​
As for filtering the user very does not need to fill a column in a separate worksheet ‼​
As it can - must ! - be directly achieved in the data worksheet !​
​
If infortunately really some VBA code seems necessary so first operate manually with Excel basics like a filter, an advanced filter, whatever …​
Once it works, restart from the beginning, activate the Macro Recorder then redo the same manual operations​
in order to get a code base, in order to learn how that works, and maybe you will see the light, how useless it is …​
​
 
Okay thanks for the help provided on the look up part. It gets a little complicated as the filter need to be applied for the column header user selected in the Main Sheet. I will figure out eventually what's wrong.tx
 
Why the user doesn't use the easy Excel filter interface without the need to create any list as the interface already has a chekboxes list ?​
​
 
Its difficult when the user has to filter manually a list of 500 records from a db2 dump of 150k rows . Hence such script in personal.xlsb file will come in handy.
 
Last edited by a moderator:
Ok but to filter only column A is necessary, column B is useless …​
This is the classic example why using Excel as a database software is very not such a great idea !​
​
 
I am referring to auto filtering in the Main Sheet based on user's selection by the list provided and not the filter in the sheet "Filterlist" which has only column A & B. Hope we are in the same page.

83603
 
Last edited by a moderator:
As I yet wrote to filter the Main sheet only FilterList column A is necessary as column B is totally useless !​
​
 
Marc L said:
As I yet wrote to filter the Main sheet only FilterList column A is necessary as column B is totally useless !​
​
Click to expand...
Sorry I could not follow which part of your script is taking the action to apply filter in the Main Sheet. as I do not see any action performed by using your script.
Also your above script is only showing all the vlook up action as Not Exist in the filterSheet. It was there yesterday and i think was later removed. something like ,""Ok"",""Not Exist"")" but not showing it above
 
Last edited:
Post #10 edited with a good enough reader new version from the Macro Recorder but according to your post #7 attachment​
you must first delete the FilterList worksheet, launch the new VBA demonstration then​
add some names whatever if exist or not only in column A then launch again the same VBA procedure.​
Issue comes only to bad readers …​
​
 
Thanks much its doing the Magic. I can add the lookup scripts for the filtersheet which you provided earlier in this. I also added a line at the bottom to remove the formula.

Code: 
.[A1].CurrentRegion.Columns(2).Formula = "=IF(ISNUMBER(MATCH(A1," _
                        & ActiveSheet.UsedRange.Columns(Selection.Column).Address(, , , True) & ",0)),""-"",""Not Exist"")"
                   .[A1].CurrentRegion.Columns(2).Value = .[A1].CurrentRegion.Columns(2).Value
 
You must log in or register to reply here.
Back
Top