# Compare two Excel columns and find out the differences

Hi,

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

------------------------
MSmith

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?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Commented:
Are you going to do this once or on several occasions?
How big are these lists?
Is performance a consideration?
0
Author 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
Commented:
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
Commented:
You can do it with Access also.

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

From AD Left Join Hyperion as H on AD.[User ID] = H.[User ID]
Where H.[User ID] Is Null
From AD Left Join Hyperion as H on AD.[User ID] = H.[User ID]

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
Author 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
Commented:
Is it safe to assume that each list will contain unique (no duplicates) entries?
0
Author 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
Commented:
Update to the query I posted.  The second select should be a right join.

From AD Left Join Hyperion as H on AD.[User ID] = H.[User ID]
Where H.[User ID] Is Null
From AD Right Join Hyperion as H on AD.[User ID] = H.[User ID]
0

Experts Exchange Solution brought to you by