Comparing two excel worksheets

Solved/Closed
squirrel - Jun 8, 2009 at 01:40 PM
lcouri Posts 1 Registration date Wednesday November 28, 2012 Status Member Last seen November 28, 2012 - Nov 28, 2012 at 06:23 PM
Hello,

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?

thanks
K
Related:

7 replies

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
Copy
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.

Regards,

Sam
62
gud effort sam, but this entire thing can be done in one formula
0
lcouri Posts 1 Registration date Wednesday November 28, 2012 Status Member Last seen November 28, 2012
Nov 28, 2012 at 06:23 PM
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'
0