Advice on how to start on my code

[shawnnnnnnn]

Hi all,

I've been tasked to write a macro that will compare 2 different worksheet. The first worksheet contains the past employee list, whereas the second worksheet contains the current employee list as of today.

The main idea for the macro is that it will look through both lists and identify the new and current employees. The current employees will be inserted into a new worksheet and the new employees will be inserted into another worksheet.

I'm looking for something similar to SQL's

SELECT * 
FROM Past 
WHERE Current.ID LIKE Past.ID

&

SELECT *
FROM Past
WHERE Current.ID NOT LIKE Past.ID

Whereby it will insert the entire row into the new worksheets.

Any advice on which function that I should be looking at?

[Rabbit]

No functions needed, assuming the spreadsheets are linked in the database, you can just use a query with an outer join.

[shawnnnnnnn]

I don't think the spreadsheets are linked to any database. Is there any other way I can go about doing this without linking the spreadsheets to an sql server?

[jforbes]

In Access, on the ribbon, select External Data then click Excel. From there you can Link to Excel Spreadsheets and they will be query-able just like a native table.

[shawnnnnnnn]

Hi guys, thanks for all your input! Really appreciate it :)

I found a way to do it without linking the spreadsheets though, I used record macro to input this a new column with this code

ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(MATCH(C[-50],Sheet1!C[-50],0)),"""",""Duplicate"")"

With that code, I recorded another macro that will filter out all the rows with "Duplicate" and copy and paste them in a new spreadsheet. Same goes for the non-duplicates.