Comparing two excel worksheets [Solved/Closed]

- - Latest reply: lcouri
Registration date
Wednesday November 28, 2012
Last seen
November 28, 2012
- Nov 28, 2012 at 06:23 PM

I am trying to find an easy way to compare and extract data from two excel worksheets.

This is the scenario.. I have an excel worksheet with a list of employee names and their relevant information. I have another excel worksheet with a list of employee names that have accounts in a system. I would like to know what employee doesn't have an account in the system and what account in the system needs to be removed because they are no longer in the employees list? Follow?

See more 

7 replies

Best answer
approved by Jean-François Pillou
Thank you
I think I have something what you are looking for. I'm not an expert on excel but my way works just fine for me. I use it every week. This does not require a great skill - just a little work.

presuming you have columns (a) Emp. No. (b) Surname (c) Fname (d) Account No.

(1) Copy the main spreadsheet with a different name {say safemaster}
(2) Highlight all the data and paint all the cells with a light colour
(3) Open the second spreadsheet & copy all the data.
(4) open the newly created spreadsheet "safemaster". Paste the data from the second spreadsheet below the last row in "safemaster" (taking care that the columns of data you are pasting are the same as "safemaster").
(5) click on the small square box on top of row no. 1 and left of column A. This will highlight all your data.
(6) Sort the data either on Emp No. or (Surname+fname)

Presuming all your employee Numbers are appearing in Column A and your Row 1 is the column heading and all Emp. No.s are appearing chronologically.

(7) click on the Row 3 of the first blank column ( say column "E") and write the following formula :
=if(A3=A2, "has Account")

(8) Copy this formula from Row 3 of Column E to the end of the data

Now whenever you have an Emp No. appearing in the "Safemaster" spreadsheet appears in the data of the second spread sheet - it will say "Has Account", those who do not have account will say "False".

(9)Click on column E - which will highlight the whole column.
Click Edit
Paste Special

In paste Special choose option "Value".

(10) Highlight the whole spreadsheet and sort it on Column E

(11) Here are your results - all those with "False" written in column E will be those who do not have Account Number.

I know it is a tidious process but when I have a limited knowledge, it works fine for me.

Hope it works for you.



Say "Thank you" 61

A few words of thanks would be greatly appreciated. Add comment

CCM 4332 users have said thank you to us this month

gud effort sam, but this entire thing can be done in one formula
Registration date
Wednesday November 28, 2012
Last seen
November 28, 2012
The problem that I experience with this solution is that it works on every other set of records. Copying it straight down will give you incorrect returns example A4 will not match A3 and return a 'false'
Registration date
Thursday September 17, 2009
Last seen
August 9, 2017
Thank you
Did you try this tip ?
It may solve your problem without using any macro
Thank you
And what about XLComparator, an easy way to compare 2 Excel sheet without coding macro ?
I would like to know the basics of Macros & to know how to compare 2 excel sheet.

vijay rao.
Registration date
Sunday April 12, 2009
Last seen
February 16, 2010
Thank you
Look below example ...try it and if there is some problem then do let me know .... 104112 look at column and match and add this text te?#2

Thank you
I forgot to add that when using DiffEngineX to do this, you may want to turn on:

Extras--->Hide Matching Rows

so you can just find out what employee names are in one list but not the other.
Registration date
Thursday January 28, 2010
Last seen
January 6, 2016
Thank you
did you look at the two solutions suggested below ?
Thank you
I have a similar task I need to do, and answer here would help me.