Comparing two excel worksheetsSolved/Closed

Question

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

sam · Accepted Answer

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

mubashir aziz · Answer

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

XLComparator · Answer

And what about XLComparator, an easy way to compare 2 Excel sheet without coding macro ?
http://www.xlcomparator.net/

Fabienne · Answer

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.

xlcomparator · Answer

Did you try this tip ? 
https://ccm.net/faq/5278-how-to-compare-two-workbooks-worksheets 
It may solve your problem without using any macro

Squirrel · Answer

I have a similar task I need to do, and answer here would help me.

rizvisa1 · Answer

did you look at the two solutions suggested below ?

Comparing two excel worksheets

7 responses

Similar discussions

Subscribe To Our Newsletter!