Comparing two excel worksheets [Solved/Closed]

squirrel - Jun 8, 2009 at 01:40 PM - Latest reply: lcouri 1 Posts Wednesday November 28, 2012Registration date November 28, 2012 Last seen
- 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
See more 

10 replies

Best answer
61
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
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

Thank you, sam 61

Something to say? Add comment

CCM has helped 1786 users this month

gud effort sam, but this entire thing can be done in one formula
lcouri 1 Posts Wednesday November 28, 2012Registration date November 28, 2012 Last seen - 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'
xlcomparator 16 Posts Thursday September 17, 2009Registration date August 9, 2017 Last seen - Dec 16, 2009 at 02:16 PM
15
Thank you
Did you try this tip ?
http://ccm.net/faq/5278-how-to-compare-two-workbooks-worksheets
It may solve your problem without using any macro
XLComparator - Aug 7, 2009 at 02:41 AM
6
Thank you
And what about XLComparator, an easy way to compare 2 Excel sheet without coding macro ?
http://www.xlcomparator.net/
I would like to know the basics of Macros & to know how to compare 2 excel sheet.

regards
vijay rao.
mubashir aziz 191 Posts Sunday April 12, 2009Registration date February 16, 2010 Last seen - Jun 8, 2009 at 10:03 PM
5
Thank you
Look below example ...try it and if there is some problem then do let me know ....


http://ccm.net/forum/affich 104112 look at column and match and add this text te?#2

5
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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 17, 2010 at 12:22 PM
4
Thank you
did you look at the two solutions suggested below ?
2
Thank you
I have a similar task I need to do, and answer here would help me.