Comparing two excel Sheets using VBA

I have a list of addresses in column A of both sheets. sheet1 and sheet2

I'm just wondering is there an efficient way to compare data in two sheets and do the following;

Remove addresses from sheet1 that are not in sheet2.

Add the addresses from sheet2 that are not in sheet1 to the end of sheet1.

My initial intention was to loop but this is apparently not efficient memory wise due to the being roughly 10000 addresses on each sheet.

Answers


Yes there is - Microsoft Query (SQL in Excel).

You can access it from Data->From Other Sources->Microsoft Query

SQL is exceptional in comparing/consolidating/updating multiple data sources. See this section of my blog post here. Microsoft Query will also be much faster than any VBA macro.

An example of your issue:

Reduce first Worksheet to items that are in both Worksheets

SELECT * FROM [Sheet1$] AS S1 INNER JOIN [Sheet2$] AS S2 on S1.Address=S2.Address

Select new addresses

SELECT S2.Address FROM [Sheet2$] AS S2 LEFT JOIN [Sheet1$] AS S1 on S1.Address=S2.Address
WHERE IsNull(S1.Address)

Join both

SELECT * FROM [Sheet1$] AS S1 INNER JOIN [Sheet2$] AS S2 on S1.Address=S2.Address
UNION ALL
SELECT S2.Address FROM [Sheet2$] AS S2 LEFT JOIN [Sheet1$] AS S1 on S1.Address=S2.Address
WHERE IsNull(S1.Address)

Need Your Help

Creating a series of timed form submissions

javascript jquery forms

I am interested in designing a timed quiz questions which submit themselves after 30 seconds or so. Following this other SO request I have coded the following:

onActivityResult not getting triggered in Android

android camera android-camera

I am making an android application, which takes an image from camera and then displays it. However, I am unable to display the clicked image probably because the onActivityResult() is not triggered.