Forum Index
shamskm.com Home
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Very easy: Compare Two Lists in Excel

 
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum
View previous topic :: View next topic  
Author Message
Admin
Site Admin


Joined: 25 Sep 2007
Posts: 252

PostPosted: Tue Jan 10, 2017 2:01 pm    Post subject: Very easy: Compare Two Lists in Excel Reply with quote

This tutorial will show you how to compare two lists using Excel's conditional formatting.

Suppose you have two lists of top European football clubs - one you prepared, the other one your friend. You will check if both the lists contain all the top clubs. (Remember, it's just an example your favorite club may not be in the list Smile)




Before, we proceed let's name our club lists.

select the range A2:A17 > on the Formulas tab > click on Define Name> Enter ListA > click OK.

Do the same for the range B2:B15 and name it ListB.






So what I want to do?

First, I want to check how many clubs from my list (ListA) are missing in my friend's list (ListB). So how do I proceed?

Select the range for ListA (A2:A17).

On the Home tab > click Conditional Formatting > click New Rule...

The 'New Formatting Rule' window will open

Here select > 'Use a formula to determine which cells to format' > enter the formula =COUNTIF(ListB;A2)=0

Here next to Preview you have the Format... button > click on it to select a Fill background color for the cells in ListA to be marked as missing in ListB. I have selected light violett color > click OK.





So now in ListA cells (or othe way, clubs) are marked that are still not included in ListB.






Do the Same for ListB

That means mark or find out the football clubs that are listed in ListB but still not in ListA.

Select the range B2:B15

On the Home tab > click Conditional Formatting > click New Rule...

The 'New Formatting Rule' window will open

Here select > 'Use a formula to determine which cells to format' > enter the formula =COUNTIF(ListA;B2)=0

Here next to Preview you have the Format... button > click on it to select a Fill background color for the cells in ListB to be marked as missing in ListA > click OK.

So the table below showing you clubs in ListB (marked light blue) that are missing in ListA.






So what does COUNTIF function do?

As you see, under conditional formatting I have used the COUNTIF function. It takes two arguments - a range, and a criteria. So its syntax is

COUNTIF(range, criteria)

It counts the number of cells (in a given range) to find if they meet your specified criteria. If COUNTIF returns a 0 (zero), that means the criteria you mentioned is not found within the specified range.

So when I enter =COUNTIF(ListB;A2)=0 it counts all the cells (in this case, for club name) in ListB that are euqal to cell A2 (or club FC Barcelona), and when it doesn't find FC Barcelona on ListB, it marks cell A2 with light violett color.

You may ask, well here I put only cell A2 as my criteria, what about other cells or club names like A3, A4, A5........A17? How they are compared?

The thing is, you have selected the complete range A2:A17 before you went to conditional formatting that means, first it looks for the criteria A2 (if it is present in ListB), if not then mark the cell with light violett color, then look for cell A3 (or club Real Madrid CF). Real Madrid CF is present in ListB, so the cell A3 is left as it is (not marked)..........and so the story goes on Smile

By the way, I am using Excel 2007.

Have fun my good friends Smile




*Recommended:

Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.


For comments of suggestions, please contact us: info@shamskm.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group