Compare two Excel columns and find out the differences

Hi,

A beginner with excel but I have two spreadsheets with the following information:

Spreadsheet #1

User ID from AD  
------------------------
MSmith

Spreadsheet #2

User ID from different Hyperion
----------------------------------------------

MSmith1

Basically I have users from two systems the users from Hyperion and users from AD. The goal is to compare the users from AD and Hyperion and find out the differences. The Hyperion users will be the reference if your AD user is different to your Hyperion user then we need to know.

I was thinking Excel might be the way to go...can anyone help me out?
llaravaAsked:
Who is Participating?
 
PatHartmanCommented:
Update to the query I posted.  The second select should be a right join.

Select AD.[User ID] As AD_ID, H.[User ID] AS H_ID
 From AD Left Join Hyperion as H on AD.[User ID] = H.[User ID]
 Where H.[User ID] Is Null
 Union Select AD.[User ID] As AD_ID, H.[User ID] AS H_ID
 From AD Right Join Hyperion as H on AD.[User ID] = H.[User ID]
 Where AD.[User ID] Is Null;
0
 
Vincent BurtonCommented:
Microsoft has an article detailing how to find duplicates in two columns. It can be changed easily enough to find unique items in each column: https://support.microsoft.com/en-us/kb/213367

Also have a look on these post might they helps you:

Find differences between two columns : http://randoltech.blogspot.in/2015/04/excel-find-differences-between-two.html

A quick way to compare columns: http://www.techrepublic.com/blog/microsoft-office/a-quick-and-dirty-way-to-compare-columns-of-excel-data/

Meanwhile try this:

Highlight column A. Click Conditional Formatting > Create New Rule > Use this formula to determine which cells to format > Enter the ff. formula:

=countif($B:$B, $A1)

Click the Format button and change the Font color to something you like.

Repeat the same for column B, except use this formula and try another font color.

=countif($A:$A, $B1)

Using a Separate Column

In column C, enter the ff. formula into the first cell and then copy it down.

=if(countif($B:$B, $A1)<>0, "-", "Not in B")

In column D, enter the ff. formula into the first cell and then copy it down.

=if(countif($A:$A, $B1)<>0, "-", "Not in A")

these help you visualize which items are missing from the other column.


Hope it helps you!!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aikimarkCommented:
Are you going to do this once or on several occasions?
How big are these lists?
Is performance a consideration?
0
 
llaravaAuthor Commented:
Are you going to do this once or on several occasions? No just once.
 How big are these lists? Less than 1 MB files.
 Is performance a consideration?  No

This is a one time thing, just to find out the differences to rename the users.
0
 
mlmccCommented:
Are you trying to make it so a user has the same name on both systems?

Are the 2 names for a user similar enough that you don't have a user with MSmith and MJones and another with MJones and MSmith?

mlmcc
0
 
PatHartmanCommented:
You can do it with Access also.

Link or import the two spreadsheets.

Create a Union query.  You will not be able to use the QBE to do this.  You MUST use SQL view.

Select AD.[User ID] As AD_ID, H.[User ID] AS H_ID
From AD Left Join Hyperion as H on AD.[User ID] = H.[User ID]
Where H.[User ID] Is Null
Union Select AD.[User ID] As AD_ID, H.[User ID] AS H_ID
From AD Left Join Hyperion as H on AD.[User ID] = H.[User ID]
Where AD.[User ID] Is Null;

This query will bring back a list of user IDs that are in the AD list but not in the Hyperion list followed by a list of User IDs that are in the Hyperion list but not in the AD list.
0
 
llaravaAuthor Commented:
Mlmcc,

Are you trying to make it so a user has the same name on both systems? yes

 Are the 2 names for a user similar enough that you don't have a user with MSmith and MJones and another with MJones and MSmith? yes, they are similar can't be more than 8 characters.

Any suggestions?
0
 
aikimarkCommented:
Is it safe to assume that each list will contain unique (no duplicates) entries?
0
 
llaravaAuthor Commented:
Is it safe to assume that each list will contain unique (no duplicates) entries?  yes, none of the systems will allow you to have duplicate entries "users"
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.