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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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
aikimarkCommented:
Are you going to do this once or on several occasions?
How big are these lists?
Is performance a consideration?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.