I need a filter to skip records.

I have sheet A with aprox 1500 lines.
I have sheet B with aprox 15 lines.
These two sheets have to be matched.
As result I want sheet A with all the lines that ar not in sheet B
so: the complete line in sheet A that's exactly in sheet B, should be
skipped.

Could it be done with a macro?

Huub

Maybe you could just insert a helper column and have a formula like:

=isnumber(match(a1,sheetB!a:a,0))
and drag down.

If it finds a match, you'll get True. No match = False.

Then you could apply data|filter|autofilter to show/hide what you want.

And Chip Pearson has a bunch of techniques for working with duplicates at:
http://www.cpearson.com/excel/duplicat.htm


Ikke = Huub wrote:

I need a filter to skip records.

I have sheet A with aprox 1500 lines.
I have sheet B with aprox 15 lines.
These two sheets have to be matched.
As result I want sheet A with all the lines that ar not in sheet B
so: the complete line in sheet A that's exactly in sheet B, should be
skipped.

Could it be done with a macro?

Huub

--

Dave Peterson

"Dave Peterson" schreef in bericht
...

=isnumber(match(a1,sheetB!a:a,0))
and drag down.

If it finds a match, you'll get True. No match = False.


Thx for your reply but:
I can't get it working.

I found out in dutch version:
isnumber = isgetal
match = vergelijken

But the arguments don't seem to be right.

What I want to do is skip a row (I'm sorry; in earlier posting I mentioned:
skip a line) if that row is completely in the other spreadsheet.

so I think first argument a1 can't be right, it should be e.g. a1:n1
and sheetB!a:a should be sheetB!a:n

But it doesn't work.
And I don't know what I'm doing wrong

Huub

How about using a helper column and concatenating all the data into one cell:

=a1&char(1)&b1&char(1)&....&char(1)&n1

Drag down.

do the same for the other worksheet.

Now use the =isnumber(match(helpercell,sheetB!helpercolumn,0))

and fill down.



Ikke = Huub wrote:

"Dave Peterson" schreef in bericht
...

=isnumber(match(a1,sheetB!a:a,0))
and drag down.

If it finds a match, you'll get True. No match = False.


Thx for your reply but:
I can't get it working.

I found out in dutch version:
isnumber = isgetal
match = vergelijken

But the arguments don't seem to be right.

What I want to do is skip a row (I'm sorry; in earlier posting I mentioned:
skip a line) if that row is completely in the other spreadsheet.

so I think first argument a1 can't be right, it should be e.g. a1:n1
and sheetB!a:a should be sheetB!a:n

But it doesn't work.
And I don't know what I'm doing wrong

Huub

--

Dave Peterson