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

SQL Upload syntax

sql sqlite

I'm struggling to get this UPDATE query to work.

Linux : How to set up "global" user / passwords / groups file in svn

linux svn configuration passwd authz

I'm trying to set up global user / group access to all my svn repositories on Linux. Since I am very new to svn / linux stuff can someone take a look at what I have set up. I still get a "Invalid a...