Author Message

Joined: 25 Sep 2007
Posts: 278

 Posted: Tue Jan 10, 2017 2:01 pm    Post subject: Very easy: Compare Two Lists in Excel 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 ) 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 By the way, I am using Excel 2007. Have fun my good friends *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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT + 2 Hours Page 1 of 1

 Jump to: Select a forum Forum Rules----------------General Rules Learn German Easily----------------German as a Foreign Language (Deutsch als Fremdsprache) Category----------------General ForumPolitics - commentsCryptocurrency Market PredictionTechnology & How-To ForumJokesScienceEnvironmental Issues forumGeographic Information System (GIS) forumWebsites Related ForumPublic Health & Yoga forum Cappuccino & Espresso----------------Cappuccino & Espresso Job Posting----------------Job offerThe Space travellers
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