How do I compare two Ranges of Data? 'Comparison Operators' are the key. Today, in Episode #1597, Bill looks at the need to compare two Ranges of Data to be ...
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Don’t Fear The Spreadsheet, Podcast Episode Number 10: Do These Cells Match?
Bill: Hey. Alright. Welcome back to Don’t Fear The Spreadsheet podcast. I’m Bill Jelen from MrExcel. Today’s question, Tyler Nash.
Tyler: Is there any way that I can compare 2 different ranges of data to see whether or not they match?
Bill: Alright. Tyler. Can we compare 2 different ranges of data? Now, this happens a lot, you know. We have a spreadsheet, we send a spreadsheet off to a co-worker, and then, later on, we give them the spreadsheet again and they need to see if we changed anything, and they could just ask us if we changed anything, but, you know, she's just not friendly, whatever. Okay. So, we need to see if SUE FLAY matches SUE FLAY. I have to give credit to Tom Urtis, one of the MVPs who wrote the book. This is his list of names. This file is straight from the list of sample files that come with a book. So, you know, for every topic, we give the file that we [use to create – 01:12]. So I just pulled up Tom’s. I think he’s a funny guy.
Anyway, so, in the last episode, episode 9, we talked about formula operators like +, -, the * for multiply, and the / for divide. There's some other operators called comparison operators, and to test if something is the same, the operator is = but there's also a >, >=, <, <=, and <>. So, this is pretty cool to be able to test to see if 2 cells are =, and what we do is, of course every formulas has to start with what? It has to start with an = sign, and we click on the first cell, cell A4, and then we say is that = to D4, alright? So, I press…I'm going to press…I'm going to hold down CONTROL when I press ENTER just to stay in the same cell, and if they're =, then we get the answer of TRUE. If they're not =, then we get the answer of FALSE, alright? Let me undo, aright? [=A4=D4]
So, we're going to take that formula. I'm going to copy that formula down and I'm going to copy that whole range across, and what we'll see is every time that the items match, we have a TRUE, and every time that the items do not match, alright? So, MAE O’NAYZ is now MAY B. SOH. Maybe she got married, I don't know. We have a FALSE, alright? So, this helps us to see which items changed or didn’t change.
Now, another way to go. I'm going to go into the next worksheet here, is to use these comparison operators right inside of something called conditional formatting, alright? Really what we want to do is we want to see the things that change. So, I'm going to use the not the same, the <, > symbol, but watch how we do this. Okay. I'm going to select the second range of data and I'm going to notice that the active cell is in D4, D4, right now. With conditional formatting, it's on the HOME tab. Just almost outside your view here, I'm going to do conditional formatting and then, down at the bottom, it says NEW RULE. NEW RULE. You can also get here with ALT+O D. ALT+O D. We want to USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT, and, remember, we're writing this formulas as it refers to D4 so we say = -- all formulas start with = -- D4 <> to A4, alright? So, go check and see if D4 is <> to A4. If they're not equal, then I want to flag it somehow, and so I'll use a…I could change the font color. I'm going to change the fill color because that is much more easy to see, click OK down here, and then click OK again, and then anything that has changed is now highlighted in red, alright? So, you know, then, as I come back and I update my data, I could have just copied and pasted there, you'll see that the red goes away. So, using conditional formatting is a good way to go. [=D4<>A4]
Let’s talk about one more use for these comparison operators. Sometimes, we want to have a formula to identify everyone who falls into a certain group. Like, for example, maybe we want to find everyone who’s over 40, 40 or over, 40 or over. That means we're going to have to use >=. So, a formula to figure out if this person is 40 or over is =, everything starts with =, the cell that we're trying to compare and then >=40, and we'll copy that formula down using the fill handle, that little square dot, and anytime that the person is over 40, we're getting a TRUE. If they're under 40, we're getting a FALSE. [=B4>=40]
Comparison operator is a cool feature to make your Excel formulas a little bit more powerful.
Tyler: Thanks for stopping by. We'll see you next episode. Check out Don't Fear The Spreadsheet. This book [unintelligible – 05:05] look like it was written for rocket scientists.
Keywords for this video: Range, Compare Ranges, Compare Two Workbooks, Compare Data, accounting, Accounting Major, Bill Jelen, Books, business, Business Major, CPA, Don't Fear The Sp...
This video is current as of August 31, 2012