Forum Discussion
Filtering between sheets
I have two Excel sheets, and I want to filter the names that appear in sheet A but not in sheet B.
In the sheet named "BETAALD," unique values are in columns C17 to C999.
In the sheet named "ALLE," all members are listed in columns A2 to A999, also with unique values.
I want to display in the "ALLE" sheet only the members that do not appear in the "BETAALD" sheet.
VBA:
Set Conn = CreateObject("ADODB.Connection")
'
Conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0; HDR=NO;IMEX=VBA:Set Conn = CreateObject("ADODB.Connection")
'
Conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0; HDR=NO;IMEX=1';Data Source=" & ThisWorkbook.FullName
Set Rst=CreateObject("ADODB.Recordset")
'sql1="select f1,* from [Sheet1$] where instr(f1&f2,?)>0"
sql1="select f1 from `ALLE$`"
sql2="(select f3 from `BETAALD$`)"
sql=sql1 & " where f1 not in " & sql2
set cmd=CreateObject("ADODB.command")
With cmd
Set .ActiveConnection = Conn
.CommandType = 1 'adCmdText
.CommandText =sql
'Set parm1 = '.CreateParameter("uName",200,1,200,"aa")
'.Parameters.Append parm1
set Rst=.Execute()
end with
msgbox sql
ActiveCell.CopyFromRecordset Rst
'FilterArr=Rst.getrows()
Rst.close
Conn.close3';Data Source=" & ThisWorkbook.FullName
Set Rst=CreateObject("ADODB.Recordset")
'sql1="select f1,* from [Sheet1$] where instr(f1&f2,?)>0"
sql1="select f1 from `ALLE$`"
sql2="(select f3 from `BETAALD$`)"sql=sql1 & " where f1 not in " & sql2
set cmd=CreateObject("ADODB.command")
With cmd
Set .ActiveConnection = Conn
.CommandType = 1 'adCmdText
.CommandText =sql
'Set parm1 = '.CreateParameter("uName",200,1,200,"aa")
'.Parameters.Append parm1set Rst=.Execute()
end with
msgbox sql
ActiveCell.CopyFromRecordset Rst
'FilterArr=Rst.getrows()
Rst.close
Conn.closeSee the attached version.
I added a helper column "Komt voor op BETAALD" with formulas, and filtered on that column.
Thank you, Hans, for your help.
But shouldn't there be 10 that are TRUE then?
Now there are quite a few more.
