Compare 2 excel files and only show new items and changes

[rhaas128]

I run a data extract daily for a set of users from one system, and import that list into a separate system. Each day the file gets larger and larger and takes more time to import/fix after it is imported. I am looking for a way to compare all columns in 2 excel sheets and only get the results of new users that were added, and also get the full row if any changes were made in a column for a user. For instance if you have the below sheets:

First	Last	UserID
Auser	AA	AAAA
Buser	BB	BBBB
Cuser	CC	CCCC

<tbody>
</tbody>

First	Last	UserID
Auser	AA	AAAA
Buser	BBBA	BBBB
Cuser	CC	CCCC
Duser	DD	DDDD

<tbody>
</tbody>

The only results I should get should be the Buser due to the change in last name, and the Duser as this is a new user.

First	Last	UserID
Buser	BBBA	BBBB
Duser	DD	DDDD

<tbody>
</tbody>

Let's say I have 15 column headers and need to compare them all, and roughly 30,000 rows. I need them all compared.

I can use spreadsheet compare for office2013, but that only shows the changes and doesn't provide the entire row. I am not sure how to do this in Access at all. The main reason I am attempting in Access is because Excel tends to lock up. But if there is a better way, I am open to any and all suggestions. Thanks in advance for any help!

 

[LockeGarmin]

An access solution might be quicker than trying to get VBA to do it. I'm not familiar with Power Query enough to know if that would be even faster. So in Access you can set up the two tables below like the following screenshot:

https://imgur.com/hMfAKhH

Or you can have the query created instantly by copying and pasting the generated SQL code in the SQL view.

SELECT Table2.UserID
    ,Table2.First
    ,Table2.Last
FROM Table1
RIGHT JOIN Table2 ON (Table1.Last = Table2.Last)
    AND (Table1.First = Table2.First)
    AND (Table1.UserID = Table2.UserID)
WHERE (((Table1.UserID) IS NULL));

Hope that helps!

 

[Micron]

Or if these are spreadsheets that are linked to the database as tables, try the Unmatched Query Wizard to find the values of one table that are not in the other. I seldom have this issue, so I can't recall the syntax (needs a WHERE NOT EXISTS predicate, I believe) or I'd take a whack at the sql statement on the fly.

 

[LockeGarmin]

Oh yeah! I always forget about the EXISTS keyword! For some reason Access has really weird rules about using parentheses around JOIN Predicates that I never have to worry about in SQL Server, but I discovered that EXISTS doesn't have that same rules so it's a lot easier to write by hand in Access. Unfortunately when you look at the EXISTS query in design view it's a bit of a mess compared to the JOIN query.

Another interesting thing: If you input the EXISTS query in SQL view:

SELECT *
FROM Table2
WHERE NOT EXISTS (
        SELECT *
        FROM Table1
        WHERE Table2.UserID = Table1.UserID
            AND Table2.First = Table1.First
            AND Table2.Last = Table1.Last
        )

Access will take the liberty of changing your query to the following:

SELECT *
FROM Table2
WHERE (
        (
            (
                EXISTS (
                    SELECT *
                    FROM Table1
                    WHERE Table2.UserID = Table1.UserID
                        AND Table2.First = Table1.First
                        AND Table2.Last = Table1.Last
                    )
                ) = False
            )
        );

Definitely advantages to both ways. Thanks for reminding me!