Advanced Filter - Copy matching records in two worksheets

[morgantobin]

I have attached an example of what I am trying to do with an Advanced filter.
I am trying to find a way to filter using Advanced filter to copy into Sheet3 records from Sheet1 that do not appear in Sheet2, based on comparison of only Col2 (as details in Col1, etc.... may change).


Any ideas/help?

[Kevin@Radstock]

Hi morgantobin

This is a formula approach!

In A2 on Sheet3:
Your profile indicates Excel 2010, you can take advantage of the aggregate function (Non Array formula enter the formula as normal)

=IFERROR(INDEX(Sheet1!A$2:A$12,AGGREGATE(15,6,ROW( Sheet1!$2:$12)-ROW(Sheet1!$1:$1)/(ISNUMBER(MATCH(Sheet1!$B$2:$B$12,Sheet2!$B$2:$B$1 2,0))),ROW($A1))),"")
Copy across to B2 and down.

For Excel 2007<
=IFERROR(INDEX(Sheet1!A$2:A$12,SMALL(IF(ISNUMBER(M ATCH(Sheet1!$B$2:$B$12,Sheet2!$B$2:$B$12,0)),ROW(S heet1!$2:$12)-ROW(Sheet1!$1:$1)),ROW($A1))),"")

This is a array formula, to commit CTRL + SHIFT + ENTER copy across to B2 and down.

Kevin