Comparing two columns in Excel

We are taking care of around 1000 servers located on Eagan. Many of the servers were registered in Tivoli Storage Manage server where all the backup data will be stored and backup scheduler configured on all the servers which requires backup. Our US counterpart took a list of servers registered with TSM software. I was told to compile these 2 list and give the report of number of servers registered in TSM server and number of servers not registered on TSM from the server list.

As i stated earlier serverlist.xls having 1000 servers listed and TSMserver.xl having 720 registered server lists. Its tough task to compare one by one from the serverlist to TSM, so i googled it to find the formula which gives the results. I thought of sharing this thing with you all which may help you in some other time in your work.

Its better to explain to you with example. As per the picture below Column A contains the list of servers from the serverlist and Column B contains the list of servers not registered in TSM.



Use this formula on column C2 to check and list whether the server A2 registed with the TSM server. You can drag the formula to A22 to check whether all the servers.

=IF(ISERROR(MATCH(A2,$B$2:$B$14,0)),"",A2)

Use this formula on column D2 to list out the servers not registered in TSM server. Drag the forumula to till D22 to list out all other servers from the server list.

=IF(ISERROR(VLOOKUP(A2,$B$2:$B$14,1,FALSE)),A2,"")

I hope you understand this concept and do let me know if you need any more detail.
Comparing two columns in Excel Comparing two columns in Excel Reviewed by Nilavan on Sunday, May 09, 2010 Rating: 5
Powered by Blogger.