Filtering between sheets

[kevinsoete]

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.

Link to find example file here.

Thank you for your help.

[#2]

@kevinsoete 

See the attached version.

I added a helper column "Komt voor op BETAALD" with formulas, and filtered on that column.

[#3]

Thank you, Hans, for your help.
But shouldn't there be 10 that are TRUE then?
Now there are quite a few more.

[#4]

@kevinsoete 

You're correct! I made a mistake in the formula.

See the corrected version below.

[#5]

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 parm1

set Rst=.Execute()
end with
msgbox sql

ActiveCell.CopyFromRecordset Rst
'FilterArr=Rst.getrows()
Rst.close
Conn.close