Comparing two excel Sheets using VBA -

i have list of addresses in column of both sheets. sheet1 , sheet2

i'm wondering there efficient way compare data in 2 sheets , following;

remove addresses sheet1 not in sheet2.

add addresses sheet2 not in sheet1 end of sheet1.

my initial intention loop apparently not efficient memory wise due being 10000 addresses on each sheet.

yes there - microsoft query (sql in excel).

you can access data->from other sources->microsoft query

sql exceptional in comparing/consolidating/updating multiple data sources. see section of blog post here. microsoft query faster vba macro.

an example of issue:

reduce first worksheet items in both worksheets

select * [sheet1$] s1 inner join [sheet2$] s2 on s1.address=s2.address 

select new addresses

select s2.address [sheet2$] s2 left join [sheet1$] s1 on s1.address=s2.address isnull(s1.address) 

join both

select * [sheet1$] s1 inner join [sheet2$] s2 on s1.address=s2.address union select s2.address [sheet2$] s2 left join [sheet1$] s1 on s1.address=s2.address isnull(s1.address)