Comparing two excel Sheets using VBA

Question

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.

Are you sure you need VBA? Have you considered adding a MATCH formula in another column in sheet1 looking for exact matches, sorting in ascending order to group all the #N/As and deleting those rows? — Mark Fitzgerald

AnalystCave.com AnalystCave.com · Accepted Answer · 2015-05-08T08:56:13

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)

Comparing two excel Sheets using VBA

1 Answers